Question

Locked

VBA EXCEL COPY BETWEEN WORKSHEETS

By grhys ·
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 ?
Thanks
Grhys

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Possible Help...

by jgarcia102066 In reply to VBA EXCEL COPY BETWEEN WO ...

Whenever I have run into an issue like this, I make the source worksheet the active worksheet and perform the copy then I make the destination worksheet the active worksheet, make the Cell(1,1) the active cell and paste.

Your code would look like this:

=========== CODE ===================
ActiveWorkbook.Sheets(2).Activate()
ActiveWorkbook.Sheets(2).Range(Cells(ConsultantRow, BegDayCol), Cells(ConsultantRow, EndDayCol)).Select
Selection.Copy
Workbooks(1).Sheets(2).Activate
Workbooks(1).Sheets(2).Range(Cells(ConsultantRow, BegDayCol), Cells(ConsultantRow, EndDayCol)).Select
Workbooks(1).Sheets(2).Paste
--------------------------

Hope this helps.

Collapse -

Aftter copying first worksheet of first Excel Sheet .exception

by adityamandlekar In reply to Possible Help...

var xlApp, xlWB, xlSheet,f2,fso,oBook,oSheet,oBook1,oSheet1,idTmr;

xlApp = new ActiveXObject("Excel.Application");
fso = new ActiveXObject("Scripting.FileSystemObject");

if(fso.FileExists("c:\\Example.XLS"))
{
f2=fso.GetFile("c:\\Example.XLS");
f2.Delete();
}
xlApp.Visible = true;
xlApp.DisplayAlerts=false
xlWB = xlApp.Workbooks.Add();
xlWB.Sheets.Add(null,xlWB.Sheets(xlWB.Sheets.Count));
xlWB.WorkSheets(1).Name="Redmond_Enabled";
xlWB.WorkSheets(2).Name="Redmond_Disabled";
xlWB.WorkSheets(3).Name="PHX_Enabled";
xlWB.WorkSheets(4).Name="PHX_Disabled";

xlSheet = xlWB.WorkSheets(1);
xlSheet.Activate();

xlSheet.Cells(1,1).Value = "job name";
xlSheet.Cells(1,2).Value = "job ID";

xlSheet.Cells(2,1).Value = "a";
xlSheet.Cells(2,2).Value = "a1";

xlSheet.Cells(3,1).Value = "b";
xlSheet.Cells(3,2).Value = "b1";

xlSheet.Cells(4,1).Value = "c";
xlSheet.Cells(4,2).Value = "c1";

xlSheet = xlWB.WorkSheets(2);

xlSheet.Activate();
xlSheet.Cells(1,1).Value = "job name";
xlSheet.Cells(1,2).Value = "job ID";

xlSheet.Cells(2,1).Value = "a";
xlSheet.Cells(2,2).Value = "a1";

xlSheet.Cells(3,1).Value = "b";
xlSheet.Cells(3,2).Value = "b1";

xlSheet.Cells(4,1).Value = "c";
xlSheet.Cells(4,2).Value = "c1";


var oExcelApp = new ActiveXObject("Excel.Application");
var oWkBks = oExcelApp.Workbooks;
oWkBks.open("c:\\Example_PHX.xlsx");

oBook = oWkBks.Item(1);
oSheet = oBook.Worksheets.Item(1);


oSheet.Activate();
oSheet.Cells.Select();

oExcelApp.Selection.Copy();

xlSheet = xlWB.WorkSheets(3);
xlSheet.Activate();
xlSheet.Paste();


oBook = oWkBks.Item(2);
oSheet = oBook.Worksheets.Item(2);


oSheet.Activate();
oSheet.Cells.Select();

oExcelApp.Selection.Copy();

xlSheet = xlWB.WorkSheets(4);
xlSheet.Activate();
xlSheet.Paste();

xlSheet.SaveAS("C:\\Example.XLSx");
xlApp.Quit();
xlApp = null;
CollectGarbage(1500);

After copying 1st worksheet to the 3rd worksheet,it throws Exception..Please suggest where I am doing wrong...Is there any problmem of garbage collector

Please reply me to adityamandlekar@yahoo.com or v-admand@microsoft.com if possible..its urgent!!!

Collapse -

Pasting Data between worksheets

by gavin.johnson In reply to VBA EXCEL COPY BETWEEN WO ...

Hi,

It may be because of the area where you code resides.

Move all copy/paste code to a module...
you will not get the Run Time 1004 error (Which occurs because you cannot change worksheets at run time if the code is nested inside a worksheet)

hope this helps.
Gav

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums