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

StuckInWork

New Member
Joined
Jul 30, 2021
Messages
6
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 Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top