How to manipulate a relative range in different workbook with a macro

[Guest_imported]

My problem is this: I,m making a macro where i copy different columns of differents workbooks to new workbook, and what i,m doing it's ok, but my problem is that the number of rows to select in another worksheets is relative, always is changing, because the workbooks are actualized, every week, so i can,t select the exactly range every time thet i run the macro, one thing that i think that happened is that the control of the macro lost, because i try to select one range in another workbook.

I hope that you can help me, thenk you., the moment that i select one cell in another workbook i can,t select the range with a cycle.
Example:

For i = 0 To UBound(WBName)
Set xlWB = xlApp.Workbooks.Open(WBPath & WBName(i), False)
xlWB.Sheets(WSName(i)).Select
xlWB.Sheets(WSName(i)).Range("a1&quot.Select
While xlWB.Sheets(WSName(i)).ActiveCell <> &quot;&quot;
contador = contador + 1
ActiveCell.Offset(1, 0).Select
Wend
cpyRnge = Array(&quot;A1:C & contador&quot
'cpyRnge = Array(&quot;xlWB.sheets(WSName(i)).Range(selection, Selection.End(xlDown)).Select&quot
xlWB.Sheets(WSName(i)).Range(cpyRnge(i)).Copy
Sheets(&quot;SideCombo&quot.Range(pstRnge(i)).Select
ActiveSheet.Paste
xlWB.Close
Next i

 

[kevinclark]

If the number of rows from the source files will vary and if each column from the source files is being pasted to a different column in the target file, then maybe you can just copy the entire column and not worry about finding the first blank cell (finding a blank cell may not always indicate the last row of data)

Example - code from

thread707-285957 to copy entire column


Sub Sample()
Dim xlApp As Object
Dim xlWB As Workbook
Dim WBPath As String
Dim WBName 'Array of Workbooks to open
Dim WSName 'Array of Worksheets to copy from
Dim cpyRnge 'Array of ranges to be copied
Dim pstRnge 'Array of ranges to be pasted to

Set xlApp = CreateObject(&quot;Excel.Application&quot
xlApp.AskToUpdateLinks = False
xlApp.DisplayAlerts = False
'xlApp.Visible = True
WBPath = &quot;C:\Development\Excel\&quot;
WBName = Array(&quot;Book1.xls&quot;, &quot;Book2.xls&quot
WSName = Array(&quot;Sheet1&quot;, &quot;Sheet1&quot
'NOTE CHANGE
cpyRnge = Array(&quot;A:A&quot;, &quot;B:B&quot
pstRnge = Array(&quot;A1&quot;, &quot;B1&quot
For i = 0 To UBound(WBName)
Set xlWB = xlApp.Workbooks.Open(WBPath & WBName(i), False)
'NOTE CHANGE
xlWB.Sheets(WSName(i)).Columns(cpyRnge(i)).Copy
Sheets(&quot;Sheet1&quot.Range(pstRnge(i)).Select
ActiveSheet.Paste
xlWB.Close
Next i

xlApp.AskToUpdateLinks = True
xlApp.DisplayAlerts = True
Set xlApp = Nothing
End Sub