macro to import multiple sheets from closed file to open file

[Ali M]

Hi experts
I have open file and the same time another closed file in the same folder .
in the closed file contains multiple sheets and different structure data with buttons and shapes the closed file is xlsm extension
the open file contains list in column G contains sheets names .
the macro should import sheets based on column G from closed file with the same arranging based on column G and when run the macro every time then just replace data for sheets have already existed , not add sheets every time when run the macro except there is new sheet should import based on column G
the files are existed in this directory "C:\Users\NAME\Desktop\

 

[John_w]

Try this macro. The sheet names start in G2.

Public Sub Import_Named_Sheets_From_Workbook()

    Dim fromWorkbookFileName As Variant
    Dim openWb As Workbook, fromWb As Workbook
    Dim importSheets As Variant
    Dim sheetName As Variant
        
    'Choose closed workbook
    
    fromWorkbookFileName = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm", Title:="Select closed workbook")
    If fromWorkbookFileName = False Then Exit Sub
    
    Set openWb = ActiveWorkbook
    
    With openWb.ActiveSheet
        importSheets = .Range("G2:G" & .Cells(.Rows.Count, "G").End(xlUp).Row).Value
    End With
    
    Application.ScreenUpdating = False
    
    'Open the closed workbook
    
    Set fromWb = Workbooks.Open(Filename:=fromWorkbookFileName)
    
    With openWb
        For Each sheetName In importSheets
            Application.DisplayAlerts = False
            On Error Resume Next
            .Sheets(sheetName).Delete
            On Error GoTo 0
            Application.DisplayAlerts = True
            fromWb.Sheets(sheetName).Copy After:=.Sheets(.Sheets.Count)
        Next
    End With
        
    fromWb.Close savechanges:=False

    Application.ScreenUpdating = False

End Sub

 

[Ali M]

Hi John,
thanks for the macro , it works greatly !
just there is problem if the sheet is not existed will show mismatch error or subscript out of range and crash the file . I want ignoring any sheet is not existed .

 

[John_w]

just there is problem if the sheet is not existed will show mismatch error or subscript out of range and crash the file . I want ignoring any sheet is not existed .

Simply move On Error GoTo 0 immediately above Next.

 

[Ali M]

I can't know where is debug error in which line, sorry !
it will crash after show error .

still shows mismatch error

 

[John_w]

Where have you moved the On Error GoTo 0 to? It should be this:

Public Sub Import_Named_Sheets_From_Workbook()

    Dim fromWorkbookFileName As Variant
    Dim openWb As Workbook, fromWb As Workbook
    Dim importSheets As Variant
    Dim sheetName As Variant
        
    'Choose closed workbook
    
    fromWorkbookFileName = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm", Title:="Select closed workbook")
    If fromWorkbookFileName = False Then Exit Sub
    
    Set openWb = ActiveWorkbook
    
    With openWb.ActiveSheet
        importSheets = .Range("G2:G" & .Cells(.Rows.Count, "G").End(xlUp).Row).Value
    End With
    
    Application.ScreenUpdating = False
    
    'Open the closed workbook
    
    Set fromWb = Workbooks.Open(Filename:=fromWorkbookFileName)
    
    With openWb
        For Each sheetName In importSheets
            Application.DisplayAlerts = False
            On Error Resume Next
            .Sheets(sheetName).Delete
            Application.DisplayAlerts = True
            fromWb.Sheets(sheetName).Copy After:=.Sheets(.Sheets.Count)
            On Error GoTo 0
        Next
    End With
        
    fromWb.Close savechanges:=False

    Application.ScreenUpdating = False

End Sub

 

[Ali M]

I know no what my bad when asked me put the line On Error GoTo 0 ,sorry
now it works excellently without problem.
thank you so much .