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

MBD

New Member
Joined
Aug 25, 2021
Messages
11
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
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
11
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
65,664
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
11
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
65,664
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
11
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
65,664
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,632
Messages
5,742,232
Members
423,714
Latest member
ftp2jz

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