VBA: How to import multiple worksheets into 1 workbook, separate tabs

[StuckInWork]

Hi all, hoping someone can help me, I have seen variations of this code everywhere but nothing that works for what I require it, there's always one thing which make's the other code not relevant to my workbook.

Essentially, I will have a main workbook which my VBA macros will be written in, I want to be able to select and import multiple files (filetype is irrelevant, but in this case it's .xls files) into the main workbook.
The intention is the VBA will open the file browser, user will batch select the required files (around 5-6), the macro will then copy the first tab/sheet from each newly opened workbook, paste each sheet into a new, separate tab on the main workbook (tab name is irrelevant) and then close all of the workbooks leaving only the main (VBA) workbook open.
From there I will be free to write the remaining VBA for formatting as required.

I do not wish for the VBA to point to a specific folder as all users have their own file arrangements, I don't need the VBA to search for a specific file type.
Some of the files will also have merged cells, so no formatting of the sheets is required (delimiting etc), just a straight copy/paste or duplication of the merged cell sheet into the main workbook.

Thanks in advance if anyone is able to help.

 

[mumps]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = True
        If .Show = -1 Then
            For Each vSelectedItem In .SelectedItems
                Set srcWB = Workbooks.Open(vSelectedItem)
                Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
                srcWB.Close False
            Next
            Else
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[StuckInWork]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = True
        If .Show = -1 Then
            For Each vSelectedItem In .SelectedItems
                Set srcWB = Workbooks.Open(vSelectedItem)
                Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
                srcWB.Close False
            Next
            Else
        End If
    End With
    Application.ScreenUpdating = True
End Sub

That is absolutely perfect thankyou.

 

[mumps]

You are very welcome.

 

[prinnaik]

Dear Mr.Excel,

Thank you for always helping.

This solution has been helpful also to my own process automation project. But I need to bring in 3 sheets from the same workbook at the time,.e.g. sheet4, sheet7 and may sheet14 into the new workbook.

I have been having this challenge, can you please help.

warm regards.

 

[mumps]

Hi and welcome to the Forum. Please start your own new thread. Use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Include a link to this thread if you think it is useful.