VBA EXCEL COPY BETWEEN WORKBOOKS

[Guest]

VBA EXCEL COPY BETWEEN WORKBOOKS
I am trying to consolidate from multiple Excel workbooks loaded individually
into a single master spreadsheet via VBA code.
The code fragment following works if I reference within the workbook(2), but
fails if I copy from workbook(2) and paste across to the master workbook(1).
One web reference suggested it was due to separate instances of Excel - that
fitted the error, but loading both files from Excel File, Open menu did not
fix it.

=========== CODE ===================
ActiveWorkbook.Sheets(2).Range(Cells(ConsultantRow, BegDayCol),
Cells(ConsultantRow, EndDayCol)).Select
Selection.Copy 'Destination:=Workbooks(1).Sheets(2).Cells(ConsultantRow,
BegDayCol)
' Paste data to MASTER
Workbooks(1).Sheets(2).Paste
Destination:=Workbooks(1).Sheets(2).Range(Cells(ConsultantRow, BegDayCol),
Cells(ConsultantRow, EndDayCol))
--------------------------
Run-time error '1004'
Application-defined or object-defined error
===================== END CODE ============

I can reference the name of the worksheet(1) from worksheet(2) code, but
cannot seem to paste to worksheet(1). What's up ?

NB The code is in the Sheet Object of workbook(2).worksheet(2), initiated by
a Button on the worksheet

Thanks
Grhys

 

[merjet]

The error occurs because Cells at the Destination aren't qualified.
Incidentally, you don't need to use Select at all. Try this:

Set ws = Workbooks(1).Sheets(2)
ActiveWorkbook.Sheets(2).Range(Cells(ConsultantRow, _
BegDayCol), Cells(ConsultantRow, EndDayCol)).Copy _
Destination:=ws.Cells(ConsultantRow, BegDayCol)

Hth,
Merjet

 

[Guest]

Thanks Merjet - it worked at long last - I tried so many variations.
The subtlety of the syntax eh!
I had tried that variant, but specifying the range as the destination
I had even tried (worksheets(1).etcetc = worksheets(2).etcetc) but that
failed.