Copy content from a specific sheet in a workbook to another specific sheet in another workbook in same directory

Question

Background:
I have two workbooks in the same directory with different sheets in each one of them.

I would like to open book2.xlsx, execute a VBA, to copy the whole content from "sheet1" in book1.xls. After this, the book1.xls should be closed automatically.

I have a code, which is moving the content next to a sheet, then I have to rename this sheet to the desired one. The problem with this is one is I the formulas in the other sheet will not work as desired. The code is as follows,

Sub XLVBACopyFiles()
    Dim MonthlyWB As Variant
    Dim FileName As String

    FileName = ActiveWorkbook.Name

    Path = ActiveWorkbook.Path & "\"

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    'Copy the sheet1 next to sheet2 in the current workbook

    Application.Workbooks.Open (Path & "book1.xls")
        Sheets(Array("sheet1")).Select
        Sheets("sheet1").Activate
        Sheets(Array("sheet1")).Move After:=Workbooks( _
            FileName).Sheets("sheet2")

    Application.EnableEvents = True
    Application.DisplayAlerts = True

    Workbooks(FileName).Save
    ' Workbooks(FileName).Close
End Sub

Any help with this would be highly appreciated.

Could you give a couple of sample sheets with the offending formulas?. I have tested the sample code and found no errors... — Antonio Rodulfo
Hello Antonio, The problem with this formulae is, that it will place the sheet next to "sheet2" because of the following line in the script (After:=Workbooks( _ FileName).Sheets("sheet2"))). However, I want it to be pasted to the sheet2 itself. Hope this, helps. Thank you. — KishoreG15

Paulo Paulo · Accepted Answer · 2019-03-18T14:28:44

If what you want , according to your comment above, is paste the content to "sheet2 itself", update the code above :

Application.Workbooks.Open (Path & "book1.xls")
        Sheets(Array("sheet1")).Select
        Sheets("sheet1").Activate
        **Sheets(Array("sheet1")).Move After:=Workbooks( _
            FileName).Sheets("sheet2")**

to

  Application.Workbooks.Open (Path & "book1.xls")
            Sheets(Array("sheet1")).Select
            Sheets("sheet1").Activate
            'Next 2 lines will select the range of content to be copied, and CTRL+C it. Edit it to your desire range
            Range("A1:A5").Select
            Selection.Copy
            Sheets("Sheet2").Select
            Range("A1").Select ' In this line you should choose which cell to start pasting
            ActiveSheet.Paste

Also, to close workbook, use:

Workbooks("book1.xls").Close SaveChanges:=True

Pay attention to SaveChanges option, choose True/False if you want to save or not this workbook

Copy content from a specific sheet in a workbook to another specific sheet in another workbook in same directory

1 Answers