Renaming of Worksheets from Input Data

[Guest]

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.

 

[Tom Ogilvy]

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.

 

[STEVE BELL]

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

 

[Guest]

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]

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