Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming of Worksheets from Input Data
Renaming of Worksheets from Input Data
Hi, Sorry to be reposting, I m desperate. I have struggled on for ever it seems trying to finalise this scoring program, and just at the last gasp €“ it all went haywire. Naturally. I had a macro working (thanks to 'bj') which renamed the tabs to show data input onto the Relevant Set Up sheet in each workbook on the sheet called SET UP. So as contestants names were entered the tabs changed to relate to those names. Since I moved them all to one workbook although everything else works, the macros dont Sub shnm() For i = 2 To 6 Sheets(i).Name = Sheets(1).Cells(I + 7, 2) Next i End Sub I altered the For I = 2 To 6 to relate to the smaller range of sheet tabs in the other workbooks, that was fine I added a MASTER page which is now the first sheet in the workbook where one of 8 options can be selected using macros based on '.visible' so when an option is clicked on the MASTER page at start up the only tabs showing are the selected option eg., Set Up1, 1,2,3,4,5, Results, through to '8' Masters is then hidden as well. This works fine. The original renaming macro doesnt work now that the individual books are all part of one book. The first and last sheets did not need renaming, the middle five/four/three/two needed to be renamed according to the input on Set Up 1/2/3/4/5/6/7/8. The tabs looks like this. Set Up 1, 1, 2, 3, 4, 5, Results 1 Set Up 2, 21, 22, 23, 24, 25, Results 2 Set Up 3, 31, 32, 33, 34, Results 3) Set Up 4, 41, 42, 43, 44, Results 4 Set Up 5, 51, 52, 53, Results 5 Set Up 6, 61, 62, 63, Results 6 Set Up 7, 71, 72, Results 7 Set Up 8, 81, 82, Results 8 Is this new first sheet 'MASTERS' whats causing the problem or is it something to do with the hidden sheets, or how many there are of them now. I am totally desperate, can anyone out there help, please. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming of Worksheets from Input Data
Perhaps you can help yourself.
Sub shnm() j = 2 For i = 2 To 6 sheets(i).Activate msgbox "this is index " & i & vbNewline _ & "Name: " & sheets(i).Name 'Sheets(i).Name = Sheets("Master").Cells(j + 7, 2) j = j + 2 Next i End Sub Run this macro and see what sheets are refered to. Change the i = 2 to 6 to perhaps i = 4 to 8 until you get the right set of sheets. then you can uncomment the renaming line. I have added a variable j to refer to the cells originally refered to. I have used "Master" as the location of those cells (that hold the new names). Change that to refer to the actual sheet that contains the new names. -- Regards, Tom Ogilvy "SYBS" wrote in message ... Renaming of Worksheets from Input Data Hi, Sorry to be reposting, I' m desperate. I have struggled on for ever it seems trying to finalise this scoring program, and just at the last gasp - it all went haywire. Naturally. I had a macro working (thanks to 'bj') which renamed the tabs to show data input onto the Relevant Set Up sheet in each workbook on the sheet called SET UP. So as contestants names were entered the tabs changed to relate to those names. Since I moved them all to one workbook although everything else works, the macro's don't Sub shnm() For i = 2 To 6 Sheets(i).Name = Sheets(1).Cells(I + 7, 2) Next i End Sub I altered the For I = 2 To 6 to relate to the smaller range of sheet tabs in the other workbooks, that was fine I added a MASTER page which is now the first sheet in the workbook where one of 8 options can be selected using macros based on '.visible' so when an option is clicked on the MASTER page at start up the only tabs showing are the selected option eg., Set Up1, 1,2,3,4,5, Results, through to '8' Masters is then hidden as well. This works fine. The original renaming macro doesn't work now that the individual books are all part of one book. The first and last sheets did not need renaming, the middle five/four/three/two needed to be renamed according to the input on Set Up 1/2/3/4/5/6/7/8. The tabs looks like this. Set Up 1, 1, 2, 3, 4, 5, Results 1 Set Up 2, 21, 22, 23, 24, 25, Results 2 Set Up 3, 31, 32, 33, 34, Results 3) Set Up 4, 41, 42, 43, 44, Results 4 Set Up 5, 51, 52, 53, Results 5 Set Up 6, 61, 62, 63, Results 6 Set Up 7, 71, 72, Results 7 Set Up 8, 81, 82, Results 8 Is this new first sheet 'MASTERS' what's causing the problem or is it something to do with the hidden sheets, or how many there are of them now. I am totally desperate, can anyone out there help, please. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming of Worksheets from Input Data
The trick is finding where the macros fall-down.
Do you have Option Explicit at the top of all modules? This will force errors that will help you find problems. Have you compiled your code? This further finds errors. Next - step through your code with F8 and monitor variable values and actual code performance. (you can put a Breakpoint at the start of the code and once the code starts, it will pause and allow you to use F8.) Double check all your sheet and range references in the code. Some of these may have changed since you moved everything into a new book. Let us know what you find... -- steveB Remove "AYN" from email to respond "SYBS" wrote in message ... Renaming of Worksheets from Input Data Hi, Sorry to be reposting, I' m desperate. I have struggled on for ever it seems trying to finalise this scoring program, and just at the last gasp - it all went haywire. Naturally. I had a macro working (thanks to 'bj') which renamed the tabs to show data input onto the Relevant Set Up sheet in each workbook on the sheet called SET UP. So as contestants names were entered the tabs changed to relate to those names. Since I moved them all to one workbook although everything else works, the macro's don't Sub shnm() For i = 2 To 6 Sheets(i).Name = Sheets(1).Cells(I + 7, 2) Next i End Sub I altered the For I = 2 To 6 to relate to the smaller range of sheet tabs in the other workbooks, that was fine I added a MASTER page which is now the first sheet in the workbook where one of 8 options can be selected using macros based on '.visible' so when an option is clicked on the MASTER page at start up the only tabs showing are the selected option eg., Set Up1, 1,2,3,4,5, Results, through to '8' Masters is then hidden as well. This works fine. The original renaming macro doesn't work now that the individual books are all part of one book. The first and last sheets did not need renaming, the middle five/four/three/two needed to be renamed according to the input on Set Up 1/2/3/4/5/6/7/8. The tabs looks like this. Set Up 1, 1, 2, 3, 4, 5, Results 1 Set Up 2, 21, 22, 23, 24, 25, Results 2 Set Up 3, 31, 32, 33, 34, Results 3) Set Up 4, 41, 42, 43, 44, Results 4 Set Up 5, 51, 52, 53, Results 5 Set Up 6, 61, 62, 63, Results 6 Set Up 7, 71, 72, Results 7 Set Up 8, 81, 82, Results 8 Is this new first sheet 'MASTERS' what's causing the problem or is it something to do with the hidden sheets, or how many there are of them now. I am totally desperate, can anyone out there help, please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming of Worksheets from Input Data
Hi Tom,
Thanks so very much. It works fine and I can understand where the code is from a bit better, Im afraid I am pretty new to this, but learning. Although I am now able to change the tabs to suit the entered data, the next problem is that I used macros to hide and unhide the sheets I didnt want showing. I referred to those sheets as their original numbers, (1,2,3, etc), and if option one is selected from the Masters sheet, the tabs then reflect the new name. Fine, unless the user makes an error and goes back to the Master sheet to reselect a different option. The €œvisible. + false/true thing doesnt then work and an error message shows because the originally selected tabs no longer carry the same number I used for the Visible Macros. How would I be able to refer to these sheets I want hidden (dependant upon which option is selected.), or should I be using a different means of just showing the selected option sheets. Thanks "Tom Ogilvy" wrote: Perhaps you can help yourself. Sub shnm() j = 2 For i = 2 To 6 sheets(i).Activate msgbox "this is index " & i & vbNewline _ & "Name: " & sheets(i).Name 'Sheets(i).Name = Sheets("Master").Cells(j + 7, 2) j = j + 2 Next i End Sub Run this macro and see what sheets are refered to. Change the i = 2 to 6 to perhaps i = 4 to 8 until you get the right set of sheets. then you can uncomment the renaming line. I have added a variable j to refer to the cells originally refered to. I have used "Master" as the location of those cells (that hold the new names). Change that to refer to the actual sheet that contains the new names. -- Regards, Tom Ogilvy "SYBS" wrote in message ... Renaming of Worksheets from Input Data Hi, Sorry to be reposting, I' m desperate. I have struggled on for ever it seems trying to finalise this scoring program, and just at the last gasp - it all went haywire. Naturally. I had a macro working (thanks to 'bj') which renamed the tabs to show data input onto the Relevant Set Up sheet in each workbook on the sheet called SET UP. So as contestants names were entered the tabs changed to relate to those names. Since I moved them all to one workbook although everything else works, the macro's don't Sub shnm() For i = 2 To 6 Sheets(i).Name = Sheets(1).Cells(I + 7, 2) Next i End Sub I altered the For I = 2 To 6 to relate to the smaller range of sheet tabs in the other workbooks, that was fine I added a MASTER page which is now the first sheet in the workbook where one of 8 options can be selected using macros based on '.visible' so when an option is clicked on the MASTER page at start up the only tabs showing are the selected option eg., Set Up1, 1,2,3,4,5, Results, through to '8' Masters is then hidden as well. This works fine. The original renaming macro doesn't work now that the individual books are all part of one book. The first and last sheets did not need renaming, the middle five/four/three/two needed to be renamed according to the input on Set Up 1/2/3/4/5/6/7/8. The tabs looks like this. Set Up 1, 1, 2, 3, 4, 5, Results 1 Set Up 2, 21, 22, 23, 24, 25, Results 2 Set Up 3, 31, 32, 33, 34, Results 3) Set Up 4, 41, 42, 43, 44, Results 4 Set Up 5, 51, 52, 53, Results 5 Set Up 6, 61, 62, 63, Results 6 Set Up 7, 71, 72, Results 7 Set Up 8, 81, 82, Results 8 Is this new first sheet 'MASTERS' what's causing the problem or is it something to do with the hidden sheets, or how many there are of them now. I am totally desperate, can anyone out there help, please. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming of Worksheets from Input Data
You can refer to sheets in 3 ways
Sheets(3) - the index number in the sheets collection Sheets("ABC") - the tab name displayed Sheet3 - the code name of the sheet. Generally the code name is the most robust, but usually must be hard coded. If the user doesn't rename the tabs, then perhaps the tab name is a good alternative. Alternatives include placing some distinctive characteristic on each sheet so that you can loop through the sheets and look for this characteristic. this could be a hidden sheet level name as an example (insert=Name=Define). -- Regards, Tom Ogilvy "SYBS" wrote in message ... Hi Tom, Thanks so very much. It works fine and I can understand where the code is from a bit better, Im afraid I am pretty new to this, but learning. Although I am now able to change the tabs to suit the entered data, the next problem is that I used macros to hide and unhide the sheets I didn't want showing. I referred to those sheets as their original numbers, (1,2,3, etc), and if option one is selected from the Masters sheet, the tabs then reflect the new name. Fine, unless the user makes an error and goes back to the Master sheet to reselect a different option. The "visible. + false/true thing doesn't then work and an error message shows because the originally selected tabs no longer carry the same number I used for the Visible Macros. How would I be able to refer to these sheets I want hidden (dependant upon which option is selected.), or should I be using a different means of just showing the selected option sheets. Thanks "Tom Ogilvy" wrote: Perhaps you can help yourself. Sub shnm() j = 2 For i = 2 To 6 sheets(i).Activate msgbox "this is index " & i & vbNewline _ & "Name: " & sheets(i).Name 'Sheets(i).Name = Sheets("Master").Cells(j + 7, 2) j = j + 2 Next i End Sub Run this macro and see what sheets are refered to. Change the i = 2 to 6 to perhaps i = 4 to 8 until you get the right set of sheets. then you can uncomment the renaming line. I have added a variable j to refer to the cells originally refered to. I have used "Master" as the location of those cells (that hold the new names). Change that to refer to the actual sheet that contains the new names. -- Regards, Tom Ogilvy "SYBS" wrote in message ... Renaming of Worksheets from Input Data Hi, Sorry to be reposting, I' m desperate. I have struggled on for ever it seems trying to finalise this scoring program, and just at the last gasp - it all went haywire. Naturally. I had a macro working (thanks to 'bj') which renamed the tabs to show data input onto the Relevant Set Up sheet in each workbook on the sheet called SET UP. So as contestants names were entered the tabs changed to relate to those names. Since I moved them all to one workbook although everything else works, the macro's don't Sub shnm() For i = 2 To 6 Sheets(i).Name = Sheets(1).Cells(I + 7, 2) Next i End Sub I altered the For I = 2 To 6 to relate to the smaller range of sheet tabs in the other workbooks, that was fine I added a MASTER page which is now the first sheet in the workbook where one of 8 options can be selected using macros based on '.visible' so when an option is clicked on the MASTER page at start up the only tabs showing are the selected option eg., Set Up1, 1,2,3,4,5, Results, through to '8' Masters is then hidden as well. This works fine. The original renaming macro doesn't work now that the individual books are all part of one book. The first and last sheets did not need renaming, the middle five/four/three/two needed to be renamed according to the input on Set Up 1/2/3/4/5/6/7/8. The tabs looks like this. Set Up 1, 1, 2, 3, 4, 5, Results 1 Set Up 2, 21, 22, 23, 24, 25, Results 2 Set Up 3, 31, 32, 33, 34, Results 3) Set Up 4, 41, 42, 43, 44, Results 4 Set Up 5, 51, 52, 53, Results 5 Set Up 6, 61, 62, 63, Results 6 Set Up 7, 71, 72, Results 7 Set Up 8, 81, 82, Results 8 Is this new first sheet 'MASTERS' what's causing the problem or is it something to do with the hidden sheets, or how many there are of them now. I am totally desperate, can anyone out there help, please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming worksheets | Excel Discussion (Misc queries) | |||
Comparing worksheets & input data | Excel Worksheet Functions | |||
How do you connect worksheets,input data in one place&it shows on | Excel Worksheet Functions | |||
Opening a template form & renaming it from a cell input automatica | Excel Discussion (Misc queries) | |||
Data entry once, input into two worksheets | Excel Discussion (Misc queries) |