Excel tutorials

5. Rename the first one in the order of the workbook in multiple workbooks within one folder

Continue tutorials from the previous web page. In this part of the tutorial, in the third order I will show how we can rename worksheet 'Sheet1' in the workbook name if we have multiple workbooks that are in the same folder. If you've been following the tutorial from the start, you've seen that I've created a template.xlsm workbook that contains traffic information. The first in the order of 'Sheet1' is the main worksheet and it must have the name of the workbook (Sheet Tab Name).

In this part of the tutorial, see a VBA macro that we will in every Excel file in the same folder, change the name of the first worksheet "Sheet1" in the name of the workbook. All workbooks are in the same folder. In the previous tutorial, you may have noticed that I have created multiple workbooks in the 'A' column. We continue with the renaming of the first worksheet (Worksheet that has the first position in workbook).

Copy this VBA macro to a VBA Standard Module1 with some newly opened workbook. Start it and notice that all the first worksheets that had the SheetTabName 'Sheet1' were renamed in the name, from the workbook name.

Option Explicit
'published by www.ic-ims.com
Sub RenSheets()
'https://www.mrexcel.com
'published by VoG
Dim MyFolder As String
Dim MyFile As String
Dim wbname As String
MyFolder = "C:\Temp\ccc\" 'define folder - change to suit
MyFile = Dir(MyFolder & "\*.xlsm") ' format file

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False

Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
With ActiveWorkbook
wbname = Left(.Name, InStr(.Name, ".") - 1)
.Sheets(1).Name = wbname 'rename this Worksheet on 1st position
.Close savechanges:=True
End With
MyFile = Dir
Loop

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True
'Message Box when tasks are completed
MsgBox "Task Completed!"
End Sub

After running VBA macros, wait a few minutes for a message to appear that the task is complete (Task Completed!). I applied this VBA macro to 100 Excel files. This went on for 1-2 minutes.

Now our individual workbook looks like the picture below.

Change the worksheet name to multiple books at once

The next step will be to delete a specific 'Sheet Tab Name' in multiple workbooks in the same folder. In this case, 'Sheet5' should be removed from each workbook in the same folder. This workbook does not need us anymore. He was here only temporarily and through it we have created multiple workbooks named with the names in the list in the 'A' column.

List all topics related to complete tutorials

  1. Creating Top Folders and Sub Folders in One Step
  2. How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
  3. Rename the first in the order of the worksheet, in multiple workbooks in the same folder
  4. How to remove the last Worksheet in multiple Workbooks in the same folder
  5. How to remove a particular VBA module in multiple workbooks in the same folder
  6. How to save all workbooks as values without formulas in all sub-folders

Click to continue tutorials: ------ >>>>>> The next part of the tutorial Item 4 from list above