drjohn23
10-15-2010, 02:36 AM
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):
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
ACTUAL DATA (doesn't work - doesn't copy anything):
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
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.
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):
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
ACTUAL DATA (doesn't work - doesn't copy anything):
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
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.