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

MBD

New Member
Joined
Aug 25, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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"

VBA Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 25, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff, apologies for the late reply. The sheets will overwrite existing data.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
VBA Code:
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
 
Solution

MBD

New Member
Joined
Aug 25, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Oops, change the 1 on this line to i
VBA Code:
FilePath = Qry.SelectedItems(1)
 
  • Like
Reactions: MBD

MBD

New Member
Joined
Aug 25, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Works like a charm. Thank you so much, Fluff for your help. You're a star!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
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,043
Messages
5,835,088
Members
430,340
Latest member
xoxe1337

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