I have the following code to open up the source data files and where the sheets are the same to paste these into the same sheets on the destination file. The user havs to select the file twice and then select the macro again to select the next file and select it twice
I would like the code amended to allow the user the select the first file and then be given the open to select another file until no more files to select
Your assistance is most appreciated
I would like the code amended to allow the user the select the first file and then be given the open to select another file until no more files to select
Code:
Sub Copy_Source_Data_SameSheets()
Dim flder As FileDialog, FileName As String, FileChosen As Integer, desWB As Workbook, ws As Worksheet
Set desWB = ThisWorkbook
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.Title = "Please select -South Sept Files."
With flder
.Filters.Clear
.Filters.Add "Excel files", "*.xlsm"
.InitialFileName = "C:\My Documents\*South*Sept*.*" 'change to suit one's needs
.Show
End With
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set srcWB = Workbooks.Open(FileName)
For Each ws In srcWB.Sheets
On Error Resume Next
ws.UsedRange.Copy desWB.Sheets(ws.Name).Range("A1")
Next ws
ActiveWorkbook.Close False
End Sub
Your assistance is most appreciated