Copy excel Sheet from one workbook to anoter

Posted on 2012-09-07
Last Modified: 2012-09-07
My destination workbook is C:\ExcelReport\SEE_REPORT_201296_dest.xlsx
and I want to get the worksheet "SEE Report2_source" from workbook C:\ExcelReport\SEE_REPORT_201296_source.xlsx

 Sub MergeSheets(ByVal x As Integer)
        Dim SrcBook As Excel.Workbook
        Dim fso As Object, f As Object, ff As Object, f1 As Object

        objExcel = New Excel.ApplicationClass
        objExcel.ScreenUpdating = False
        fso = CreateObject("Scripting.FileSystemObject")
        f = fso.Getfolder("C:\ExcelReport\")
        ff = f.Files
        objRange = objWorksheet.UsedRange
        For Each f1 In ff
            'objExcel = New Excel.ApplicationClass
            objWorkbook = objExcel.Workbooks.Open("C:\ExcelReport\SEE_REPORT_201296_dest")
            objExcel.Visible = True
            ' SrcBook = objExcel.Excel.Workbooks.Open("C:\ExcelReport\SEE_REPORT_201296.xlsx")
            objRange.Range("A1:IV" & objRange.Range("A65536").End(Excel.XlDirection.xlUp).Row).Copy()
            ' objWorkbook.ThisWorkbook.Worksheets(1).Activate()
            objRange.Range("A65536").End(Excel.XlDirection.xlUp).Offset(1, 0).PasteSpecial()
            objExcel.Application.CutCopyMode = False

    End Sub

Open in new window

Question by:mikensu12
    LVL 33

    Expert Comment

    If you had references to the 2 workbooks you should just be able to do this.

      objWorkbook = objExcel.Workbooks.Open("C:\ExcelReport\SEE_REPORT_201296_dest")
      srcWorkbook = objExcel.Workbooks.Open("C:\ExcelReport\SEE_REPORT_201296_source.xlsx")
      srcWorkbook.Worksheets("SEE Report2_source").Copy (objWorkbook.Worksheets(1))

    Open in new window

    LVL 33

    Accepted Solution

    Thought I'd add this which copies the sheet after the last sheet in the destination workbook.
            srcWorkbook.Worksheets("SEE Report2_source").Copy(After:=dstWorkbook.Worksheets(dstWorkbook.Worksheets.Count))

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Title # Comments Views Activity
    excel forecast function 1 27
    Best charting add on for .Net Applications 2 17
    Import csv files to MS SQL 5 34
    Search Box 13 21
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now