im new to this whole macro thing in excell. im trying to generate about 6 different projects with 40-50 sheets in each one and im trying to be able to automatcally change the name for each sheet so i dont have to click every single one of them. its for an appartment building which x amount of rooms in them and need to change each sheet to that room number. anyone have any idea how to do this? thanks in advance for this
Excell 2010 Auto naming sheets(7 posts)
also wondering how to change a number in a cell in the sheet so it automatically does it for every sheet in each project so i can make this go by a lot faster then clicking and changing everyone of them. thanks again in advance
Need more information before proceeding. How do you want the sheets renamed? Do you just want to start renaming at 1 and go up for each one that exists? Base the new sheet name on a cell in the actual sheet?
Can you provide an example?
i need the sheet names to start as rm 100 and go up to rm 147 then the next set go from rm 200 to rm 347 automatically and the same thing as the sheet names have a cell that says what room it is to change with every sheet so i dont have to physically put them in for every one of them. ill try getting you an example of it for you
column a column b column c column d
Barracks : HP 513 Room: 300
Nomenclature Qty Required Servicable Un-Servicable
Desk Lamp 1
Floor Lamp 1
Night Stand 2
Window Block 1
with RM (whatever it is) as the name of the sheet
what i am trying to do is get the room numbers to automatically be inputted & change for the sheet names
as well as the number for Room: (whatever it is) to change as well from 100 to 147
ive got 2 buildings: 1 with 48 rooms each floor starting at 100 and ending at 147
and so on and so on for each deck and the second building ranged from 100 to 139 (40 rooms per floor), i am needing to do
an inventory of all the rooms and check to make sure everything is good and generate a document with each list with their
perspective room for the whole floor for each building. trying to figure out a way to do it so i dont have to do it 264 times lol thanks in advance
I found this code for renaming sheets based on a cell within the sheets themselves Rename Worksheets.
Excerpting the rename code:
Sub RenameTabs() For i = 1 To Sheets.Count If Worksheets(i).Range("D1").Value <> "" Then Sheets(i).Name = "RM"&Worksheets(i).Range("D1").Value End If Next End Sub
This code will rename each tab with RM and add the room number from cell D1 in each worksheet. For example: RM300, RM301, etc.
The page this is on explains about how to execute it, but if you have any difficulties, don't hesitate to ask away. Someone will be able to help you.
nrsanders09, if I understand what you are trying to do, I would use a macro along the lines of this:
Only did it for 10 rooms but you'll get the idea.
Dim RoomNum As Integer
Dim SheetName As String
Dim ws As Worksheet
RoomNum = 100 'starting room number
Do While RoomNum < 111 'last room number + 1
Set ws = Sheets.Add(After:=Sheets(Worksheets.Count))
SheetName = "RM " & RoomNum
ws.Name = SheetName
'anything you want on every sheet here
.Range("D1").Value = RoomNum
.Range("A5").Value = "Blinds"
.Range("A6").Value = "Carpet"
.Range("a7").Value = "Curtain"
RoomNum = RoomNum + 1 'increase room number for next loop
This topic has been closed to new replies.