Automatically importing sheets from one workbook to another

[hugedomer11]

I have a program that outputs data into an Excel spreadsheet (we'll call it Workbook 1) that has 17 worksheets. The format of the sheets is always the same but the number of rows of data entries varies.

Then I have another Excel file (Workbook 2) that has 2 sheets of calculations on it. Is there a way to show Workbook 2 the file path of Workbook 1 and have it add the 17 worksheets after the existing 2 worksheets? I don't need Workbook 2 to continuously update as changes are made to Workbook 1, it would just have to happen once.

Thanks in advance for your help.

[tigeravatar]

hugedomer11,

You should be able to use the following macro to accomplish what you're looking for:

Sub ImportSheetsMacro_for_hugedomer11()
    
    Static wbDataPath As String: wbDataPath = Application.GetOpenFilename("Excel Files, *.xls*")
    If wbDataPath = "False" Then Exit Sub
    Application.ScreenUpdating = False
    
    Static wbDest As Workbook: Set wbDest = ActiveWorkbook
    Static wbData As Workbook: Set wbData = Workbooks.Open(wbDataPath)
    
    Dim ws As Worksheet
    For Each ws In wbData.Sheets
        ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
    Next ws
    wbData.Close False
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar

[hugedomer11]

@tigeravatar

Thanks for the help! This is exactly what I was looking for. It worked the first time with no problems.