Merging a workbook into an open workbook

[mhp620]

Hi.. does anyone have a macro that allows the user to select a workbook(in a dialog box) and then copies all the sheets in the selected workbook into the open workbook?

Thanks in advance. Mike

 

[Norie]

Mike

Something like this perhaps.

Option Explicit
 
Sub CopyAllSheets()
Dim wbThis As Workbook
Dim wbOpen As Workbook
Dim varFileName As Variant

    varFileName = Application.GetOpenFilename("Excel Files (*.xls), (*.xlsx) , *.xls, *.xlsx")
    
    If TypeName(varFileName) = "String" Then
    
        Set wbThis = ThisWorkbook
        
        Set wbOpen = Workbooks.Open(varFileName)
        
        wbOpen.Sheets.Copy After:=wbThis.Worksheets(wbThis.Worksheets.Count)
        
        wbOpen.Close False
    
    End If
    
End Sub

Note, this works but it's a bit of a mess - I can never remember the right syntax for the file filter part of GetOpenFileName.