Question

  • Creator
    Topic
  • #2251846

    VBA EXCEL COPY BETWEEN WORKSHEETS

    Locked

    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

All Answers

  • Author
    Replies
    • #2499311

      Clarifications

      by grhys ·

      In reply to VBA EXCEL COPY BETWEEN WORKSHEETS

      Clarifications

    • #2499279

      Possible Help…

      by jgarcia102066 ·

      In reply to VBA EXCEL COPY BETWEEN WORKSHEETS

      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.

      • #2515074

        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!!!

    • #2646291

      Pasting Data between worksheets

      by gavin.johnson ·

      In reply to VBA EXCEL COPY BETWEEN WORKSHEETS

      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

Viewing 2 reply threads