I have this code, and just want to change the sheet Name for every file imported.
This code is working very very fine and also importing the multi pal files as well.
Need to modify code that every file which is imported should be rename as "Data", "Data1","Data2" and so on.
if 2 files are imported then the sheetname will be "Data" and "Data1"
if 3 files are imported then the sheetname will be "Data" and "Data1" and "Data2"
Any help.
This code is working very very fine and also importing the multi pal files as well.
Need to modify code that every file which is imported should be rename as "Data", "Data1","Data2" and so on.
if 2 files are imported then the sheetname will be "Data" and "Data1"
if 3 files are imported then the sheetname will be "Data" and "Data1" and "Data2"
Any help.
VBA Code:
Sub Merge()
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
For i = 1 To tempFileDialog.SelectedItems.Count
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
sourceWorkbook.Close
Next i
End Sub