Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA EXCEL COPY BETWEEN WORKBOOKS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA EXCEL COPY BETWEEN WORKBOOKS
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA EXCEL COPY BETWEEN WORKBOOKS
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. -- Thanks Gareth Rhys MCSE RSACE NSA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy folder with excel linked workbooks | Excel Discussion (Misc queries) | |||
Copy links between excel workbooks using citrix? | Excel Worksheet Functions | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Excel 2003 cannot copy between workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Excel 2003 Workbooks | Excel Discussion (Misc queries) |