VBA: Copy tabs from several workbooks to One

[vzq032372]

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

 

[Dan_W]

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]

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]

I updated the previous code with the following and it works like I need it to. How do I paste values?

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