Transpose cell range into sheet names

nickharg

New Member
Joined
May 24, 2012
Messages
18
Does anyone know if it's possible to transpose a vertical range of cells in, say, Sheet1, so that the names of new worksheets are automatically changed?

For example: Have the names of Sheets 2-6 automatically change to equal the values in range A1:A5 in Sheet1

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this
Code:
Sub RenSht()
Dim i As Long
With ActiveWorkbook
For i = 1 To Worksheets.count - 1
    Worksheets(i).Name = Worksheets(Worksheets.count).Range("A" & i).Value
Next i
End With

End Sub
 
Upvote 0
Sorry to do this to you, but I really have no understanding of how Macros are built.

To be more specific than I was in my example:

I need to change the names of Sheets 6-45 to equal the cell range of A3:A52 in Sheet1

Could you repost your solution using these references?
 
Upvote 0
Pardon me for jumping in, but I am curious about something. Please don't take this wrong as you are not the only one who does this (no by a long shot), but given this...

...I really have no understanding of how Macros are built.
Why would you post your question this way...

For example: Have the names of Sheets 2-6 automatically change to equal the values in range A1:A5 in Sheet1
Instead of just saying this in the first place...

To be more specific than I was in my example:

I need to change the names of Sheets 6-45 to equal the cell range of A3:A52 in Sheet1
 
Upvote 0
You're right, I should have done better the first time around. Combination of carelessness and inexperience I suppose. Regardless, the correct information is out there now so some specific help (tailored to my workbook) would be much appreciated.
 
Upvote 0
You're right, I should have done better the first time around. Combination of carelessness and inexperience I suppose. Regardless, the correct information is out there now so some specific help (tailored to my workbook) would be much appreciated.
I think this will do what you want...

Code:
Sub RenameSheets()
  Dim X As Long
  For X = 3 To 52
    Worksheets(X + 3).Name = Worksheets("Sheet1").Range("A" & X).Value
  Next
End Sub
The reason I say "think" is you said "I need to change the names of Sheets 6-45 to equal the cell range of A3:A52 in Sheet1" but unfortunately you specified less sheets than cells with new sheet names, so I assumed you wanted to use all the cells in the specified range. Although I think you may have simplified things again and may actually mean cells A3 to the end of data in Column A whether that is cell A52 or not... but I'll await clarification on this from you if you need to do so.
 
Upvote 0
Yeah, whoops, it's A3:52 to Sheets 6-55. I ran your macro but it comes back saying:
---------------------
Run-time error '9':

Subscript out of range
---------------------

Just to make sure I'm doing this right - I open up VBA and with "VBAProject (*WorkbookName*)" highlighted I go Insert --> Module --> paste your code in.

Then I go back to my workbook --> Developer --> Macros --> Run the macro (It comes up as RenameSheets)
 
Upvote 0
Yeah, whoops, it's A3:52 to Sheets 6-55. I ran your macro but it comes back saying:
---------------------
Run-time error '9':

Subscript out of range
Do you have 55 worksheets in your workbook (to be renamed) or is the macro supposed to create them for you?

Just to make sure I'm doing this right - I open up VBA and with "VBAProject (*WorkbookName*)" highlighted I go Insert --> Module --> paste your code in.

Then I go back to my workbook --> Developer --> Macros --> Run the macro (It comes up as RenameSheets)
Yes, that sounds right.
 
Upvote 0
I already have 55 worksheets created. 1-5 should keep their current names and 6-55 should change with what is in A3:A52 in Sheet1. Does it matter if all my worksheets are currently named (Rather than Sheet1, Sheet2, etc)? I have already manually named all 55.
 
Upvote 0
I already have 55 worksheets created. 1-5 should keep their current names and 6-55 should change with what is in A3:A52 in Sheet1. Does it matter if all my worksheets are currently named (Rather than Sheet1, Sheet2, etc)? I have already manually named all 55.
No, the names of the sheets that are being renamed are immaterial; however, the sheet name with A3:A52 on it is another matter... that one I reference inside the loop. I used the name Sheet1 because that is what you said it was in the first and second messages that you posted. Was using Sheet1 in place of the sheet's real name another "simplification" by you?
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top