CODEWITHSUNDEEP
excelvba
Gun hanwei
Gun hanwei

Reputation: 93

Merge specific workbooks into one

Sub MergeWorkbooks()

    Dim Path            As String
    Dim FileName        As String
    Dim ws              As Worksheet
    
    Dim wb As Workbook

    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "C:\Users\Name\Documents\Data\"
    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
        Set wb = Workbooks.Open(FileName:=Path & "\" & FileName)
        For Each ws In wb.Worksheets
            ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
        Next ws
        wb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I have 2 workbooks in same directory. Workbook 1 contain sheet A only and Workbook 2 contains sheets B and C. How can I merge sheet A and sheet C to my current workbook?

Upvotes: 0

Views: 35

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Make sure you open your source workbooks and then use the Worksheet.Move method or the Worksheet.Copy method to move or copy them into your current workbook.

Dim SourceWb1 As Workbook
Set SourceWb1 = Workbooks.Open(FileName:="C:\Path\To\Your\workbook1.xls")

SourceWb1.Worksheets("Sheet A").Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

Dim SourceWb2 As Workbook
Set SourceWb2 = Workbooks.Open(FileName:="C:\Path\To\Your\workbook2.xls")

SourceWb2.Worksheets("Sheet C").Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

If you used the .Move method make sure you don't forget to save your source workbooks:

SourceWb1.Close SaveChanges:=True
SourceWb2.Close SaveChanges:=True

If you used the .Copy method close them without saving:

SourceWb1.Close SaveChanges:=False
SourceWb2.Close SaveChanges:=False

Upvotes: 1

Related Questions