Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have a list of loan numbers that I want to use to rename worksheet tabs,
any way to do that? |
#2
![]() |
|||
|
|||
![]()
I have used the following previously:-
Sub Rename() Sheets("old name").Select Sheets("old name").Name = "new name" End Sub Hope this helps "Waki" wrote: I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
#3
![]() |
|||
|
|||
![]()
Thanks, but I'm new to VB...I'm going to need a little more help.
"Pank Mehta" wrote: I have used the following previously:- Sub Rename() Sheets("old name").Select Sheets("old name").Name = "new name" End Sub Hope this helps "Waki" wrote: I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
#4
![]() |
|||
|
|||
![]()
Hi Waki
the first approach assumes that you don't have the sheets in your workbook yet and will add the sheet in and name it from your list of loan numbers ....(instructions on using the code are at the bottom) Assuming your loan numbers are in the range A2:A10 inclusive Sub newws() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Set NewSheet = Sheets.Add NewSheet.Name = arr(i, 1) Next i End Sub --------- this second option assumes that you have all the sheets in the workbook and you want to name them as per the list in A2:A10 Sub namesheets() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Sheets(i + 1).Activate Sheets(i).Name = arr(i,1) Next i End Sub ------- to use either of the above, right mouse click on a sheet tab and choose view code this will display the VBE Window choose insert / module from the menu and you'll get a piece of white paper on the right hand side of the screen copy and paste the code above from the word sub to the words end sub into the right hand side of the screen press ALT & F11 to return to your workbook ensure your loan numbers are in cells A2:A10 of the sheet you're viewing and choose tools / macro / macros from the menu choose either namesheets or newws as appropriate and click on the run button hope this helps Cheers JulieD "Waki" wrote in message ... I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
#5
![]() |
|||
|
|||
![]()
Thanks!!!!!
"JulieD" wrote: Hi Waki the first approach assumes that you don't have the sheets in your workbook yet and will add the sheet in and name it from your list of loan numbers ....(instructions on using the code are at the bottom) Assuming your loan numbers are in the range A2:A10 inclusive Sub newws() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Set NewSheet = Sheets.Add NewSheet.Name = arr(i, 1) Next i End Sub --------- this second option assumes that you have all the sheets in the workbook and you want to name them as per the list in A2:A10 Sub namesheets() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Sheets(i + 1).Activate Sheets(i).Name = arr(i,1) Next i End Sub ------- to use either of the above, right mouse click on a sheet tab and choose view code this will display the VBE Window choose insert / module from the menu and you'll get a piece of white paper on the right hand side of the screen copy and paste the code above from the word sub to the words end sub into the right hand side of the screen press ALT & F11 to return to your workbook ensure your loan numbers are in cells A2:A10 of the sheet you're viewing and choose tools / macro / macros from the menu choose either namesheets or newws as appropriate and click on the run button hope this helps Cheers JulieD "Waki" wrote in message ... I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
#6
![]() |
|||
|
|||
![]()
you're welcome and thanks for the feedback
"Waki" wrote in message ... Thanks!!!!! "JulieD" wrote: Hi Waki the first approach assumes that you don't have the sheets in your workbook yet and will add the sheet in and name it from your list of loan numbers ....(instructions on using the code are at the bottom) Assuming your loan numbers are in the range A2:A10 inclusive Sub newws() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Set NewSheet = Sheets.Add NewSheet.Name = arr(i, 1) Next i End Sub --------- this second option assumes that you have all the sheets in the workbook and you want to name them as per the list in A2:A10 Sub namesheets() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Sheets(i + 1).Activate Sheets(i).Name = arr(i,1) Next i End Sub ------- to use either of the above, right mouse click on a sheet tab and choose view code this will display the VBE Window choose insert / module from the menu and you'll get a piece of white paper on the right hand side of the screen copy and paste the code above from the word sub to the words end sub into the right hand side of the screen press ALT & F11 to return to your workbook ensure your loan numbers are in cells A2:A10 of the sheet you're viewing and choose tools / macro / macros from the menu choose either namesheets or newws as appropriate and click on the run button hope this helps Cheers JulieD "Waki" wrote in message ... I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Julie,
I really like this idea, as I can use it quite extensively. However, I have a summary sheet that I use to reference my sheets, Sheet1, Sheet2, Sheet3, etc. The formula that I use for summary sheet is =INDIRECT("Sheet"&Row()-3&"!B!2"). How would I use this code (or change it), so that it changes the sheet reference to the new sheet name? The summary sheet is actually titled "ResidualSummary". The reason I ask, is because if I am looking for errors, I can list the customer names (instead of loan numbers) and then the ResidualSummary sheet will show the customer names and then I just have to find the sheet that has the same customer name, as opposed to trying to determine which sheet the error exists on. Any help would be greatly appreciated. -- Nothing in life is ever easy - just get used to that fact. "JulieD" wrote: Hi Waki the first approach assumes that you don't have the sheets in your workbook yet and will add the sheet in and name it from your list of loan numbers ....(instructions on using the code are at the bottom) Assuming your loan numbers are in the range A2:A10 inclusive Sub newws() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Set NewSheet = Sheets.Add NewSheet.Name = arr(i, 1) Next i End Sub --------- this second option assumes that you have all the sheets in the workbook and you want to name them as per the list in A2:A10 Sub namesheets() Dim arr As Variant arr = Range("a2:a10").Value For i = LBound(arr) To UBound(arr) Sheets(i + 1).Activate Sheets(i).Name = arr(i,1) Next i End Sub ------- to use either of the above, right mouse click on a sheet tab and choose view code this will display the VBE Window choose insert / module from the menu and you'll get a piece of white paper on the right hand side of the screen copy and paste the code above from the word sub to the words end sub into the right hand side of the screen press ALT & F11 to return to your workbook ensure your loan numbers are in cells A2:A10 of the sheet you're viewing and choose tools / macro / macros from the menu choose either namesheets or newws as appropriate and click on the run button hope this helps Cheers JulieD "Waki" wrote in message ... I have a list of loan numbers that I want to use to rename worksheet tabs, any way to do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) | |||
Macro to create macro | Excel Discussion (Misc queries) | |||
Compare 2 Worksheets Create a 3rd depending on results | Excel Discussion (Misc queries) | |||
How do I create a macro of editing keystrokes? | Excel Discussion (Misc queries) | |||
Can I batch rename new worksheets | New Users to Excel |