Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Waki
 
Posts: n/a
Default how do i create a macro to rename worksheets in excel?

I have a list of loan numbers that I want to use to rename worksheet tabs,
any way to do that?
  #2   Report Post  
Pank Mehta
 
Posts: n/a
Default

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   Report Post  
Waki
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Waki
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default how do i create a macro to rename worksheets in excel?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CREATE MACRO TO COPY MULTIPLE WORKSHEETS Bewilderd jim Excel Discussion (Misc queries) 5 March 3rd 05 11:00 PM
Macro to create macro Carl Bowman Excel Discussion (Misc queries) 2 February 6th 05 11:49 AM
Compare 2 Worksheets Create a 3rd depending on results Kevin Excel Discussion (Misc queries) 1 February 5th 05 12:49 AM
How do I create a macro of editing keystrokes? scjanner Excel Discussion (Misc queries) 0 January 22nd 05 12:07 AM
Can I batch rename new worksheets Harry Limey New Users to Excel 3 January 19th 05 10:19 PM


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"