Open multiple selected workbooks and copy the range from each sheet into destination then print, and repeat until last selected workbook.

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I would like some help with customising this bit of code below.

What I would like this to do is to prompt the user to select 1 or more .CSV files, and copy the selected range from each file into my main workbook, print that sheet then continue to the next document that was previously select.

The code below does this for only 1 selected document.

Any help would be appreciated.

Kyle

VBA Code:
Sub GetFileCopyData()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Dim Fname As String
        Dim SrcWbk As Workbook
        Dim DestWbk As Workbook
        
        Set DestWbk = ThisWorkbook
        
        Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv*), *.csv*", Title:="Select a File")
        If Fname = "False" Then
            MsgBox "No file was selected", vbInformation
            Exit Sub
        End If
        Set SrcWbk = Workbooks.Open(Fname)
        
        SrcWbk.Sheets(1).Range("B3").Copy DestWbk.Sheets("PASTE").Range("A1")
        DestWbk.Sheets("TEMPLATE").printout preview:= False

        SrcWbk.Close False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
There are two things a user will need to know to use this code. 1) How to use the FileName dialog box to locate the applicable directory and 2) how to hold down Ctrl while seleccting multiple files. If the user can master those two items, then the code should do what you want.

VBA Code:
Sub GetFileCopyData()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Dim Fname As Variant
        Dim SrcWbk As Workbook
        Dim DestWbk As Workbook
        Set DestWbk = ThisWorkbook
        Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select 1 Or More Files", MultiSelect:=True)
        If fName = False Then
            MsgBox "No file was selected", vbInformation
            Exit Sub
        End If
        For i = LBound(Fname) To UBound(Fname)
        Set SrcWbk = Workbooks.Open(Fname(i))
        SrcWbk.Sheets(1).Range("B3").Copy DestWbk.Sheets("PASTE").Range("A1")
        DestWbk.Sheets("TEMPLATE").PrintOut preview:=False
        SrcWbk.Close False
        Next
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,207
Messages
5,640,844
Members
417,173
Latest member
Tuanphun

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