Copy excel Sheet from one workbook to anoter

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

Who is Participating?
NorieVBA ExpertCommented:
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

NorieVBA ExpertCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.