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

StuckInWork

New Member
Joined
Jul 30, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Try:
VBA Code:
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

New Member
Joined
Jul 30, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Try:
VBA Code:
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.
 

Forum statistics

Threads
1,143,907
Messages
5,721,440
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top