VBA - Copy Pasting Worksheet from Multiple Workbooks to Existing Worksheets as Value

[MBD]

Hi All,

I'm trying to build a VBA macro that will allow me to import a worksheet from up to three workbooks using a file picker.
Source workbooks will always have a sheet named "Monthly Summary", which I'd like to copy over

I have attempted to write the code below, but couldn't figure out how to do include the following:
1) Code needs to paste as values only (source worksheet has macros in it, which keeps getting copied along)
2) Code needs to paste to already existing sheets: "Monthly Summary 1", "Monthly Summary 2" and "Monthly Summary 3"

Sub ImportFiles()

    Dim Qry As FileDialog
    Dim FilePath, SheetName As String

    Set Qry = Application.FileDialog(msoFileDialogFilePicker)
        Qry.Title = "Select Files to Compare " & FileType
        Qry.AllowMultiSelect = True
        Qry.Filters.Clear
        Qry.Show

        If Qry.SelectedItems.Count = 1 Then
           FilePath = Qry.SelectedItems(1)
        End If

    For i = 1 To Qry.SelectedItems.Count

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    FilePath = Qry.SelectedItems(1)

    SheetName1 = "Monthly Summary"

    Set wb2 = Workbooks.Open(FilePath)

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

    Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Any help is greatly appreciated.

Regards,

MBD

 

[Fluff]

Will the sheets that you copy to be blank? If not should the new data overwrite what's there, or add it below the existing data.

 

[MBD]

Hi Fluff, apologies for the late reply. The sheets will overwrite existing data.

 

[Fluff]

Thanks for that, how about

Sub ImportFiles()

    Dim Qry As FileDialog
    Dim FilePath As String, SheetName1 As String
    Dim i As Long
    Dim wb2 As Workbook
    
    Set Qry = Application.FileDialog(msoFileDialogFilePicker)
        Qry.Title = "Select Files to Compare "
        Qry.AllowMultiSelect = True
        Qry.Filters.Clear
        Qry.Show

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    For i = 1 To Qry.SelectedItems.Count


    FilePath = Qry.SelectedItems(1)

    SheetName1 = "Monthly Summary"

    Set wb2 = Workbooks.Open(FilePath)

      With ThisWorkbook.Sheets(SheetName1 & " " & i)
        .UsedRange.ClearContents
        wb2.Sheets(SheetName1).UsedRange.Copy
        .Range("A1").PasteSpecial xlPasteValues
      End With
    Next i

Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

 

[MBD]

Thank you for this, Fluff.
I have tried the modified code - I think mostly it works fine, except for some reason it only copied the first selected wb2 sheet, and pasted it to the three existing sheets (duplicated the paste).

Any idea what may caused this?

 

[Fluff]

Oops, change the 1 on this line to i

FilePath = Qry.SelectedItems(1)

 

[MBD]

Works like a charm. Thank you so much, Fluff for your help. You're a star!

 

[Fluff]

You're welcome & thanks for the feedback.