How to get the Workbook name from a variable sheet name

Chandana_Gadugu

New Member
Joined
Dec 2, 2009
Messages
24
Hi,

I have an Active sheet which is

MySName = ActiveSheet.Name

Here MySName gives the active sheet name.

I require 1 more variable to take this MySName and assign it to a workbook name and make it is as .xls as i required this workbook name to be used later to close the workbook

For ex:
MySName = ActiveSheet.Name

this gives MySName =aaaa

Now i want like MyWBook = aaaa.xls
But aaaa i want it from variable as we cannot hardcode it.

Thanks In Advance,
Chandana.G
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
The workbook is named when you save it. You could use .SaveAs to give it the same name as the sheet.

Code:
Dim MySName As String, strPath As String

MySName = ActiveSheet.Name
strPath = "C:\MyFolder\"
ActiveWorkbook.SaveAs Filename:=strPath & MySName & ".xls"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

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
Top