Sheet Rename

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,047
Office Version
  1. 365
  2. 2021
  3. 2019
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.
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Need to modify code that every file which is imported should be rename as "Data", "Data1","Data2" and so on.
It doesn't appear that you are importing files, just copying sheets, so I assume your question is only about renaming sheets, not files. See added line of code below. I have not tested this because I don't have the appropriate files to test your code.

Rich (BB 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)
            mainWorkbook.Sheets(mainWorkbook.Worksheets.Count).Name = "Data" & IIf(i>1," " & i - 1, "")
        Next tempWorkSheet
        sourceWorkbook.Close
    Next i

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top