below code ...
... prompts the user to select a (1) source file
... the first worksheet of this source file is copied to the target file at the very beginning
... if a worksheet name in the target file matches the copied worksheet name in the source file, a MsgBox says so and the macro exits.
Could somebody help me re-write this code ...
so that the user is able to select several excel-files in the open file dialog box (from 2 to n, usually between 2 and up to 10 are selected) and ...
the first worksheet of each selected source file is then copied to the target file (currently active workbook) at the very beginning.
if a worksheet name in the target file matches one of the copied worksheet names in the source file, a MsgBox says so and the macro exits.
Help is much appreciated. Thank you very much in advance.
Private Sub Import_Data_Into_Current_Workbook_EE()
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
.ButtonName = "&Open"
.InitialFileName = "C:\MyFile\Data"
.Filters.Add "Excel files (*.xls; *.xlsm; *.xlsx)", "*.xls;*.xlsm;*.xlsx", 1
.Title = "Please Select an input file"
.AllowMultiSelect = False
If Not (Application.fileDialog(msoFileDialogOpen).Show) Then
MsgBox "No File specified!.", vbExclamation, "Cancel has been pressed!"
customerFilename = Application.fileDialog(msoFileDialogOpen).SelectedItems(1)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
On Error Resume Next
Set srcSht = targetWorkbook.Sheets(sourceSheet.Name)
On Error GoTo 0
If IsEmpty(srcSht) Then
MsgBox "Sheet name already there"