Renaming all worksheets to the same name within folder

Question

I have a folder full of CSV files. They all have the same data structure and there's only one worksheet per workbook. They all have different worksheet names, usually a date. I would like to convert all of the worksheet names to exactly the same across all workbooks, something like "RenamedSheet" (or anything really). The code I have changes the worksheet name to the name of the workbook. I just want one worksheet name across all of the workbooks selected.

Sub RenameSheet()

Dim CurrentBook As Workbook
Dim ImportFiles As FileDialog
Dim FileCount As Long 'Count of workbooks selected
Dim wbName As String

'Open File Picker
Set ImportFiles = Application.FileDialog(msoFileDialogOpen)
With ImportFiles
    .AllowMultiSelect = True
    .Title = "Pick Files to Adjust"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xlsx files", "*.xlsx"
    .Show
End With

Application.DisplayAlerts = False
Application.DisplayAlerts = False

'Cycle through books
For FileCount = 1 To ImportFiles.SelectedItems.Count
    Set CurrentBook = Workbooks.Open(ImportFiles.SelectedItems(FileCount))
        wbName = Replace(CurrentBook.Name, ".xlsx", "") ' had to rework this line to the original
        CurrentBook.Activate
        ActiveSheet.Name = wbName
        CurrentBook.Close True
Next FileCount ' had to change this to FileCount to remove the error "invalid next control variable"

Application.DisplayAlerts = True
Application.DisplayAlerts = True

End Sub

How do I change my code so that it renames all of the worksheets in the selected workbooks to let us say "Renamesheet"

Thanks for any help!

"I have a folder full of CSV files"- do you though? Looks more like you have a folder of Excel workbooks? — Tim Williams

Variatus Variatus · Accepted Answer · 2020-05-01T03:36:46

Please replace the relevant part of your code with the following.

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'Cycle through books
For FileCount = 1 To ImportFiles.SelectedItems.Count
    Set CurrentBook = Workbooks.Open(ImportFiles.SelectedItems(FileCount))
        ' Excel activates the CurrentBook on Open
        With CurrentBook
            ' if there is only one sheet its index must be 1
            .Sheets(1).Name = "New Name"
            .Close True
        End With
Next FileCount

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

Renaming all worksheets to the same name within folder

1 Answers