Combine mutiple sheets into one Workbook

Michelle Zhao 0
Viewed 31

I have two workbooks(A & B) that contain multiple sheets. I need to copy all sheets of information from Workbook A into Workbook B without impacting on originally data of Workbook B.

Any simple code can be applied to this situation via VBA.

vba excel
1 Answers

This should work. And it will add an (x) at the end of the sheet name in case of one with the same name already exists in workbook B.

Sub Worksheet_Copier()
    Dim sh As Worksheet
    Dim wAPath As String, wBPath As String
    Dim wA As Workbook, wB As Workbook
    
    wAPath = "" 'Full name (Path) of workbook A
    wBPath = "" 'Full name (Path) of workbook B
    
    Set wA = Workbooks.Open(wAPath)
    Set wB = Workbooks.Open(wBPath)

    For Each sh In wA.Worksheets
        On Error Resume Next
        sh.Copy After:=wB.Sheets(wB.Worksheets.Count)
    Next

End Sub

If you want to close files, just add at the end:

wA.Close SaveChanges:=False
wB.Close SaveChanges:=True
AlexRivax0
Related