how do i create a macro to rename worksheets in excel?

[Guest]

I have a list of loan numbers that I want to use to rename worksheet tabs,
any way to do that?

 

[Guest]

I have used the following previously:-
Sub Rename()
Sheets("old name").Select
Sheets("old name").Name = "new name"
End Sub

Hope this helps

 

[Guest]

Thanks, but I'm new to VB...I'm going to need a little more help.

 

[JulieD]

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

 

[Guest]

Thanks!!!!!

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

 

[JulieD]

you're welcome and thanks for the feedback

Thanks!!!!!

 

[KennyD]

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.