Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Naming Macro problem - help needed
Hi all,
I have a list of people names (approx 20) that I would like to use to name separate tabs in a spreadsheet. For each person, I need two named tabs (one has all the calculated data and the other has a graph of selected information). I need to set up a macro that automatically takes the names from the list and renames the tabs accordingly. If I was to reorder the list or add new names, the macro needs to automatically update itself and rename the associated tabs. For example: A1: Bob A2: Joe A3: Gary etc etc Two tabs required for each (1st: same as A1, 2nd: A1 graph) and so on. If A1 was to change to Jim and A4 was added for Bob, the tabs need to reflect this change. Hope this makes sense. Can anyone help? Thanks James -- J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Naming Macro problem - help needed
Hi,
I'm not sure I fully inderstand the bit aboutchanging the order of names which makes things very difficult because in the renaming process it is likely you would try and have 2 sheets with the same name which is illegal. To get around this the macro forst renames the shet 1,2,3 etc and then renames them using a list in column A I've included no error trapping for not enough sheets for example Sub sonic() For x = 1 To ThisWorkbook.Worksheets.Count Worksheets(x).Name = x Next y = 1 For x = 1 To ThisWorkbook.Worksheets.Count Step 2 Worksheets(x).Name = Sheets(1).Cells(y, 1).Value Worksheets(x + 1).Name = Sheets(1).Cells(y, 1).Value & " Graph" y = y + 1 Next End Sub Mike "James" wrote: Hi all, I have a list of people names (approx 20) that I would like to use to name separate tabs in a spreadsheet. For each person, I need two named tabs (one has all the calculated data and the other has a graph of selected information). I need to set up a macro that automatically takes the names from the list and renames the tabs accordingly. If I was to reorder the list or add new names, the macro needs to automatically update itself and rename the associated tabs. For example: A1: Bob A2: Joe A3: Gary etc etc Two tabs required for each (1st: same as A1, 2nd: A1 graph) and so on. If A1 was to change to Jim and A4 was added for Bob, the tabs need to reflect this change. Hope this makes sense. Can anyone help? Thanks James -- J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Naming Macro problem - help needed
Mike,
Probably just bad explaining on my part! I have changed how I might approach this and need your advice on the following: I have the following code to select sheets: Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5", "Sheet 6", _ "Sheet 7", "Sheet 8", "Sheet 9", "Sheet 10", "Sheet 11", "Sheet 12", "Sheet 13", _ "Sheet 14", "Sheet 15", "Sheet 16", "Sheet 17", "Sheet 18", "Sheet 19", "Sheet 20", _ "Sheet 21")).Select Sheets("Sheet 1").Activate I have then modified another macro that was suggested by someone else in a different thread (can't find it for reference), to: Sub ... dim wks as worksheet for each wks in activewindow.selectedsheets wks.name=wks.range("C4").value next wks End Sub Where C4 is the person's name. This seems to work for the first instance, but then the sheet references don't update themselves. I also have another tab for each person's which I need to rename aswell, but not sure how! Have you got any ideas as to where I can go from here? Thanks for your help. -- J "Mike H" wrote: Hi, I'm not sure I fully inderstand the bit aboutchanging the order of names which makes things very difficult because in the renaming process it is likely you would try and have 2 sheets with the same name which is illegal. To get around this the macro forst renames the shet 1,2,3 etc and then renames them using a list in column A I've included no error trapping for not enough sheets for example Sub sonic() For x = 1 To ThisWorkbook.Worksheets.Count Worksheets(x).Name = x Next y = 1 For x = 1 To ThisWorkbook.Worksheets.Count Step 2 Worksheets(x).Name = Sheets(1).Cells(y, 1).Value Worksheets(x + 1).Name = Sheets(1).Cells(y, 1).Value & " Graph" y = y + 1 Next End Sub Mike "James" wrote: Hi all, I have a list of people names (approx 20) that I would like to use to name separate tabs in a spreadsheet. For each person, I need two named tabs (one has all the calculated data and the other has a graph of selected information). I need to set up a macro that automatically takes the names from the list and renames the tabs accordingly. If I was to reorder the list or add new names, the macro needs to automatically update itself and rename the associated tabs. For example: A1: Bob A2: Joe A3: Gary etc etc Two tabs required for each (1st: same as A1, 2nd: A1 graph) and so on. If A1 was to change to Jim and A4 was added for Bob, the tabs need to reflect this change. Hope this makes sense. Can anyone help? Thanks James -- J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem naming range | Excel Worksheet Functions | |||
Dynamic naming of range needed | Excel Worksheet Functions | |||
Ron De Bruin Code modification needed when naming copied worksheet | Excel Programming | |||
Help needed with this autosave macro problem | Excel Programming |