VBA: Copy tabs from several workbooks to One

vzq032372

New Member
Joined
Dec 17, 2015
Messages
17
I found this code that works great to open files in a folder path and then proceed to copy the contents into a Master file. My problem is that I only want three of the tabs to be copied over, how can I change this code to do that?

Sub mergeFiles()

'Merges all files in a folder to a main file.

'Define variables:

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks

For i = 1 To tempFileDialog.SelectedItems.Count

'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'Copy each worksheet to the end of the main workbook
'THIS IS WHERE I NEED TO ADD THE CODE TO ONLY SELECT THE SPECIFIC TABS I NEED TO COPY
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

Next tempWorkSheet



'Close the source workbook
sourceWorkbook.Close

Next i



End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
Is it a certainty that all three sheets will be in each of the workbooks you open? How do you identify them? By name?
It is easy enough to do - as you point out in a comment - you can include a condition which checks to see whether each of the sheets it cycles through meets the requirements (i.e, name). Alternatively, if it's a given that the three sheets will be in each workbook, why not just hardcode copy the sheets across without having to iterate through every single sheet?
 

vzq032372

New Member
Joined
Dec 17, 2015
Messages
17
Yes, the sheets will all be in the workbooks.

Cover, Summary, Estimate

All are created in separate workbooks and those three tabs need to be copied from as many as 15 files. I only need those three tabs to cover (paste values) into the master workbook so that I can summarize them all
 

vzq032372

New Member
Joined
Dec 17, 2015
Messages
17
I updated the previous code with the following and it works like I need it to. How do I paste values?

VBA Code:
Sub CopySheets()

    Dim DialogBox As FileDialog

    Dim FilePath, SheetName As String

    Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)

        DialogBox.Title = "Select Estimates to copy " & FileType

        DialogBox.AllowMultiSelect = True

        DialogBox.Filters.Clear

        DialogBox.Show

        If DialogBox.SelectedItems.Count = 1 Then

           FilePath = DialogBox.SelectedItems(1)

        End If

    For i = 1 To DialogBox.SelectedItems.Count

Application.ScreenUpdating = False

Application.DisplayAlerts = False

    FilePath = DialogBox.SelectedItems(1)

    SheetName1 = "Cover"

    SheetName2 = "Summary"

    SheetName3 = "Estimate "

    Set closedBook = Workbooks.Open(FilePath)

    closedBook.Sheets(SheetName1).Copy After:=ThisWorkbook.Sheets(1)

    closedBook.Sheets(SheetName2).Copy After:=ThisWorkbook.Sheets(2)

    closedBook.Sheets(SheetName3).Copy After:=ThisWorkbook.Sheets(3)

    closedBook.Close SaveChanges:=False

    Next i

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,678
Messages
5,838,734
Members
430,566
Latest member
ChanchalSingh

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