Microsoft Excel Tutorial:
Copy and paste this VBA code:
Today's question from Lakshmi. She wants to bulk change all workbooks in a folder so that the Sheet name is the name of the workbook. In this special case, there is only one worksheet per workbook. I talked to Lakshmi and asked if the workbook name is C:\Customers\Apple.xlsm then the sheet name would be Apple.
This workbook shows off three skills
Table of Contents
(0:00) Welcome and Question
(0:33) Use Power Query to bring a list of files into Excel
(1:36) Launch Excel VBA Macro editor for first time. Display Project Explorer and Insert a module
(2:01) Copying and running a macro
(2:28) Wrap up
Copy and paste this VBA code:
VBA Code:
Sub ChangeSheetNameAllWorkbooks()
Dim WB As Workbook
For Each cell In Selection
Set WB = Workbooks.Open(cell.Value)
WB.Worksheets(1).Name = Left(WB.Name, Len(WB.Name) - 5)
WB.Close SaveChanges:=True
Next
End SubToday's question from Lakshmi. She wants to bulk change all workbooks in a folder so that the Sheet name is the name of the workbook. In this special case, there is only one worksheet per workbook. I talked to Lakshmi and asked if the workbook name is C:\Customers\Apple.xlsm then the sheet name would be Apple.
This workbook shows off three skills
Table of Contents
(0:00) Welcome and Question
(0:33) Use Power Query to bring a list of files into Excel
(1:36) Launch Excel VBA Macro editor for first time. Display Project Explorer and Insert a module
(2:01) Copying and running a macro
(2:28) Wrap up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2366.
Bulk change many workbooks. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question from Lakshmi on YouTube.
Can you please suggest how to replace Excel sheet name with their own file name in bulk. We talked a little bit.
If the file is called Apple.xlsx.
The first sheet (there is only one sheet per workbook according to Lakshmi) would just be called Apple. Get rid of the xlsx extension.
I set up a folder out here that has a whole bunch of files.
I want to change.
The first thing we have to do is get a list of these files in Excel.
The fast and easy way to do that. Data, Get data from File. From folder.
I will copy the address here with Ctrl c. Paste in there.
Or you could use the Browse button. Whichever you want to do. Click OK.
Choose to Transform Data.
All I want is the Folder Path and the File Name.
I choose file name and folder path. Right-click and say remove the other columns.
Then I am going to move the folder path before the name like that.
Select both columns. Choose Add Columns, Merge Columns.
The separator is none because there's a closing backslash. That is great.
Click OK. That now gives me path and file name.
Get rid of these. Remove columns. Then Home, Close and Load.
Our plan is going to be that you are going to select the list.
Let's say that there's somet file in here that you didn't want.
Go ahead and delete those rows.
Select the list and then we're going to switch over to VBA with Alt+F11.
If you've never been in VBA before, it opens up to this gray screen.
Press Ctrl+R to display the Project Explorer. Select the workbook we are in now.
S Insert, Insert Module.
We get a blank module.
Down in the YouTube description is this code. Copy the code from YouTube description and paste right here.
Click anywhere in the code. Click run.
It will open a workbook for each item in your selection. Changing the sheet name.
Close the workbook. Save changes equals TRUE.
Quick test here. We will just choose one of these. Open Mango.xlsm.
And there it is. The sheet name has been changed. That's it.
Well, if you like these videos please Like, Subscribe, and Ring that Bell. Thanks for Lakshmi for sending that question in.
Thanks to you for stopping by. We will see you next time for another netcast from MrExcel.
Bulk change many workbooks. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question from Lakshmi on YouTube.
Can you please suggest how to replace Excel sheet name with their own file name in bulk. We talked a little bit.
If the file is called Apple.xlsx.
The first sheet (there is only one sheet per workbook according to Lakshmi) would just be called Apple. Get rid of the xlsx extension.
I set up a folder out here that has a whole bunch of files.
I want to change.
The first thing we have to do is get a list of these files in Excel.
The fast and easy way to do that. Data, Get data from File. From folder.
I will copy the address here with Ctrl c. Paste in there.
Or you could use the Browse button. Whichever you want to do. Click OK.
Choose to Transform Data.
All I want is the Folder Path and the File Name.
I choose file name and folder path. Right-click and say remove the other columns.
Then I am going to move the folder path before the name like that.
Select both columns. Choose Add Columns, Merge Columns.
The separator is none because there's a closing backslash. That is great.
Click OK. That now gives me path and file name.
Get rid of these. Remove columns. Then Home, Close and Load.
Our plan is going to be that you are going to select the list.
Let's say that there's somet file in here that you didn't want.
Go ahead and delete those rows.
Select the list and then we're going to switch over to VBA with Alt+F11.
If you've never been in VBA before, it opens up to this gray screen.
Press Ctrl+R to display the Project Explorer. Select the workbook we are in now.
S Insert, Insert Module.
We get a blank module.
Down in the YouTube description is this code. Copy the code from YouTube description and paste right here.
Click anywhere in the code. Click run.
It will open a workbook for each item in your selection. Changing the sheet name.
Close the workbook. Save changes equals TRUE.
Quick test here. We will just choose one of these. Open Mango.xlsm.
And there it is. The sheet name has been changed. That's it.
Well, if you like these videos please Like, Subscribe, and Ring that Bell. Thanks for Lakshmi for sending that question in.
Thanks to you for stopping by. We will see you next time for another netcast from MrExcel.
