VBA code doesn't work on different data set

[drjohn23]

Hello everybody,

I have three xls files called: Master.xls, source1.xls and source2.xls.
There is an UPDATE button in the Master file which takes data from source1 and source2 and copy them to the Master. In the Master workbook there is also Sources worksheet where you specify the exact location of the source files.
After some testing, I've successfully written and optimized the code for this, or so I thought. The same code that worked perfectly on my test example doesn't work at all on actual data perimeter.
It doesn't give an error but it doesn't copy the data.

I think it would be easiest to understand if you look into the files here:
2shared.com/file/hfariAeI/Test3.html
2shared.com/file/oJ_Q_16F/XLS_Tool_Pr.html

Just be sure to modify the destination of the files in the sources worksheet in Master workbook.

Here is the code anyway:

TEST EXAMPLE (works fine):
[vba]Sub CopyCells3()
'----------DECLARATIONS OF THE SOURCE DIRECTORIES VARIABLES------------
Dim i, j, k, l As Integer
Dim source(100) As String
Dim sheetname(100) As String

'*** Count the number of sources and set the variables
Sheets("Sources").Activate
i = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count
For j = 1 To i
source(j) = Range("A2").Offset(j - 1).Value
sheetname(j) = Range("B2").Offset(j - 1).Value
Next j

Sheets("MasterSheet").Activate
Range(Range("A2"), Range("e2").End(xlDown)).Rows.Clear

'---------OPEN & COPY FOR ALL WORKBOOKS---------
For j = 1 To i
Workbooks.Open Filename:=source(j)
Sheets(sheetname(j)).Activate
Range(Range("A2"), Range("D2").End(xlDown)).Rows.Copy

ThisWorkbook.Activate
Sheets("MasterSheet").Activate
k = ActiveSheet.UsedRange.Rows.Count
Range("A" & k + 1).Select
ActiveSheet.Paste

l = ActiveSheet.UsedRange.Rows.Count
Range(Range("E" & k + 1), Range("E" & l)).Value = sheetname(j)
Next j

End Sub[/vba]
ACTUAL DATA (doesn't work - doesn't copy anything):
[vba]
Private Sub cmdUpdate_Click()

'----------DECLARATIONS OF THE SOURCE DIRECTORIES VARIABLES------------
Dim i, j, k, l As Integer
Dim source(100) As String
Dim wkb As Workbook

'*** Count the number of sources and set the variables
Sheets("sources_list").Activate
i = ActiveSheet.UsedRange.Rows.Count - 1
For j = 1 To i
source(j) = ActiveSheet.Range("A2").Offset(j - 1).Value
Next j

Sheets("uploading_specifics").Activate
Range(Range("A2"), Range("P2").End(xlDown)).Rows.Clear


'---------OPEN & COPY FOR ALL WORKBOOKS---------
For j = 1 To i
Workbooks.Open Filename:=source(j)
Sheets("uploading_specifics").Activate
Range(Range("A2"), Range("P2").End(xlDown)).Rows.Copy

ThisWorkbook.Activate
Sheets("uploading_specifics").Activate
k = ActiveSheet.UsedRange.Rows.Count
Range("A" & k + 1).Select
ActiveSheet.Paste
Next j

End Sub[/vba]

The only difference is that the range is wider in actual perimeter of data, and that the worksheet name of source fiels is not parametric in real set of data as I found it the sheetname of the source files will always be the same.
But you probably cannot notice the problem just by looking at the code.

Thank you.

[p45cal]

I haven't looked at your sheets, yet.
This is a button on a worksheet, yes?
So this second code is in a sheet's code module and not a standard code module? All unqualified references such as: [vba]Range(Range("A2"), Range("P2").End(xlDown)) [/vba]will refer to the sheet that the code module is written in, regardless of the active sheet/workbook.

Will post again if I get around to it, especially if you come back too, as this is 5 day old unanswered query.

[p45cal]

Looking at your files confirmed my suspicions, try:
[vba]Private Sub cmdUpdate_Click()
'----------DECLARATIONS OF THE SOURCE DIRECTORIES VARIABLES------------
Dim i As Long, j As Long, k As Long
Dim source(100) As String
Dim wkb As Workbook

'*** Count the number of sources and set the variables
With ThisWorkbook.Sheets("sources_list")
i = .UsedRange.Rows.Count - 1
For j = 1 To i
source(j) = .Range("A2").Offset(j - 1).Value
Next j
End With
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("uploading_specifics")
.Range(.Range("A2"), .Range("P2").End(xlDown)).Rows.Clear

'---------OPEN & COPY & CLOSE FOR ALL WORKBOOKS---------
For j = 1 To i
k = .UsedRange.Rows.Count
Set wkb = Workbooks.Open(Filename:=source(j))
' wkb.Sheets("uploading_specifics").Range(wkb.Sheets("uploading_specifics").R ange("A2"), wkb.Sheets("uploading_specifics").Range("P2").End(xlDown)).Rows.Copy .Range("A" & k + 1)
wkb.Sheets("uploading_specifics").Range(wkb.Sheets("uploading_specifics").R ange("A2"), wkb.Sheets("uploading_specifics").Range("P65000").End(xlUp)).Rows.Copy .Range("A" & k + 1)
wkb.Close
Next j
End With
Application.ScreenUpdating = True
End Sub[/vba] There could be a waste of time/resources in this bit:
.Range("P2").End(xlDown)).Rows.Copy
which will copy nearly the entire sheet if there's only 1 row of data (as in one of your source files), perhaps replace that bit with:
.Range("P65000").End(xlUp)).Rows.Copy
as I have done above, but revert to the commented-out line if you don't like it.