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(Consult antRow,
BegDayCol)
' Paste data to MASTER
Workbooks(1).Sheets(2).Paste
Destination:=Workbooks(1).Sheets(2).Range(Cells(Co nsultantRow, 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

--
Gareth Rhys MCSE RSACE NSA