The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

Excell 2010 Auto naming sheets

(7 posts)
  • Started 4 years ago by nrsanders09
  • Latest reply from nosparks
  • Topic Viewed 661 times

Posts: 47

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

Posted 4 years ago
Posts: 47

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

Posted 4 years ago
Posts: 566

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?

Posted 4 years ago
Posts: 47

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

Posted 4 years ago
Posts: 47

column a column b column c column d

Barracks : HP 513 Room: 300
Nomenclature Qty Required Servicable Un-Servicable
Blinds 1
Carpet 1
Chair 2
Curtain 2
Desk Lamp 1
Floor Lamp 1
Mattress 2
Microwave 1
Rack 2
Refridgerator 1
Night Stand 2
Secretary 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

Posted 4 years ago
Posts: 566

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
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.

Posted 4 years ago
Posts: 148

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.

Sub FirstSetOfSheetRoomNumbers()

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

With Sheets(SheetName)
'anything you want on every sheet here
.Range("D1").Value = RoomNum
.Range("A5").Value = "Blinds"
.Range("A6").Value = "Carpet"
.Range("a7").Value = "Curtain"
End With

RoomNum = RoomNum + 1 'increase room number for next loop


End Sub

Posted 4 years ago

Topic Closed

This topic has been closed to new replies.