Import sheets from workbook

[albertod8]

I am trying to create a macro that would import 3 sheets from a workbook that the user would select (since it will be different sourcefile every month).
We could name Worksheets in Sourcefile:
"Sheet number 1" - only columns, A,D and F to be imported
"Sheet number 2"
"Sheet number 3"

If any of the sheets is not in the file picked by the user we should show an error notification.

Thanks a lot in advance.

 

[smozgur]

The following code might help. Instructions are in the code.

Sub importWorksheets()
Dim arrSheets
Dim txtWrk As Variant
Dim txtSht As Variant
Dim wrk As Workbook
   
    arrSheets = Array("Sheet number 1", "Sheet number 2", "Sheet number 3")
   
    ' Select the source workbook to open
    txtWrk = Application.GetOpenFilename
   
    If txtWrk = False Then
        Exit Sub
    End If
   
    ' Open the source workbook
    Set wrk = Application.Workbooks.Open(txtWrk)
   
    ' Verify worksheets
    For Each txtSht In arrSheets
        If Not Evaluate("ISREF('" & txtSht & "'!A1)") Then
            MsgBox txtSht & " not found in the source workbook.", vbOKOnly + vbExclamation, "Missing Worksheet"
            wrk.Close False
            Exit Sub
        End If
    Next txtSht
   
    ' Import worksheets from the source book
    Application.ScreenUpdating = False
    For Each txtSht In arrSheets
        wrk.Worksheets(txtSht).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Next txtSht
    Application.ScreenUpdating = True

    ' Keep only A,D,F columns in the first imported worksheet
    ThisWorkbook.Worksheets(arrSheets(0)).Range("B:C,E:E,G:" & Split(Cells(1, Columns.Count).Address, "$")(1)).Delete


    ' Close the source workbook without saving it
    wrk.Close False

End Sub

 

[albertod8]

thanks a lot, exactly what i needed!

 

[smozgur]

thanks a lot, exactly what i needed!

Glad to hear it helps.

Please mark the post as the solution that answered the question to help future readers. You can simply click to the little checkmark icon right next to the post frame.