PDA

View Full Version : Copy from different workbook



DavidWaldo
02-28-2017, 05:35 AM
Hello, I want to copy data from 3 different workbooks into a sheet in an active workbook, my code works only when the target workbooks are saved on the 1.st sheet(the sheet from wich i want to copy). But if one of the target sheets was saved on 2nd. sheet it does not work. Any help?



Dim Src As Workbook, Abook As Workbook
Set Abook = ThisWorkbook

For i = LBound(names) To UBound(names)
With Sheets("***")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set Src = Workbooks.Open("path..".xlsx", True, True)
Src.Worksheets(1).ShowAllData
TLR = Src.Worksheets(1).Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Rows.Count
Src.Worksheets(1).Range("A2").Resize(TLR, 9).Copy
Abook.Sheets("***").Cells(LR + 1, 1).PasteSpecial xlPasteValues
Src.Close False
Set Src = Nothing

Next i

DavidWaldo
02-28-2017, 05:43 AM
I know the problem is in this line of code..becouse it only counts rows on the opened sheet in the target workbook not rows in the first sheet.

TLR = Src.Worksheets(1).Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Rows.Count

p45cal
02-28-2017, 06:24 AM
The problem is Worksheets(1) which will look at the first worksheet, whether it's the active/showing sheet or not.
You will need to run through the sheets to identify them in some other way (unless you know you will always want to process ALL the sheets).
Is there something about the sheet's name which could be looked at, or some content of the sheet that would enable you to be sure it's one you want to process?

DavidWaldo
02-28-2017, 06:29 AM
The sheets dont have a same name in each workbook, they are always the 1st sheet tho.

p45cal
02-28-2017, 06:34 AM
In that case Worsheets(1) should be just fine.

Oh, hold on…
TLR = Src.Worksheets(1).Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Rows.Count
should be
TLR = Src.Worksheets(1).Range("C1:C" & Src.Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row).Rows.Count
or perhaps shorter:
TLR = Src.Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row
assuming all excel files are the same version (all .xls or all .xls?) (that question mark is meant to be a wildcard).

DavidWaldo
02-28-2017, 06:36 AM
Yet it does not work when one of the workbooks was saved on second sheet, becouse TLR will count rows on active sheet in the opened workbook.

DavidWaldo
02-28-2017, 06:41 AM
YES!!!


TLR = Src.Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row

This line works like a charm. Thanks

p45cal
02-28-2017, 06:41 AM
Yet it does not work when one of the workbooks was saved on second sheet, becouse TLR will count rows on active sheet in the opened workbook.I think I've just addressed that by amending my last message.