Sheet creation and colouring the tabs

[SYBS]

I have a workbook which creates and names extra sheets dependant upon the
list of names entered on the Set Up Sheet. (Range B16:B20). These sheets
will always be Sheets 3 - 7, but not all of them will always be in use (If
for instance there are only 3 names entered and not 5), sheets 6 and 7 wont
be used on that occasion.

Managed to happily get this working in terms of the number created and
naming of those sheets , but I need to colour code the tabs of the new
sheets. I have tried

Set CompCell1 = Worksheet("Set Up").Range (B16) - and so on down to CompCell5

If not IsEmpty (CompCell1) then
Worksheets(Sheet3.Name).Tab.ColourIndex = 6

I have also tried

If Worksheets.Name = Worksheets(Sheet3.Name) then
Worksheets(Sheet3.Name).Tab.ColourIndex = 6

I have had various error messages including object not supported.

I think I am getting mixed up with how I should refer to the sheets, but I
cant use the Tab names allocated from the entered list because I want this
workbook to be reused many times for different competitions, so have tried to
use their original names to miss that problem. I also am unsure of what type
of sub to use and dont really understand the differences too well, and where
to put it.

It's a really slow process getting the head around VBA but any help from
anyone would be appreciated, I am hoping to learn as I go on.

Thanks in advance

Sybs

 

[Mike]

The syntax is

Sheets("Sheet3").Tab.ColorIndex = 3

Mike

 

[Gary''s Student]

You don't need either the name or sheet position. When the sheet is created,
it is the Active sheet. Use something like:

Sub demo()
MsgBox (ActiveSheet.Name)
ActiveSheet.Tab.ColorIndex = 35
End Sub

 

[SYBS]

Thanks for that. Have I overcomplicated things here ? The set up page holds
the data to create the scoring sheets (from 3 - 5 sheets) These are created
by a macro which takes the info in the relevant cell and creates and names
each sheet. This happens right at the beginning, so I am not accessing the
new sheets immediately. I would like the tab colours of these new sheets to
be created at the same time as the sheets without any user input. And where
do I put the right code. Sorry if I am not very bright about this but I am
pretty new to it.

More explanation would be great please.

Thanks

 

[Bob Phillips]

In the cell next to the name, add the colorindex value, then select just the
names and run this

Dim i As Long
Dim cell As Range
Dim sh As Worksheet

With Worksheets
For Each cell In Selection
Set sh = .Add(after:=Worksheets(.Count))
sh.Name = cell.Value
sh.Tab.ColorIndex = cell.Offset(0, 1).Value
Next cell
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SYBS]

Thank you so much. I will try this and get back with results.

Thanks

Sybs

 

[SYBS]

Hello Bob,

Sorry to be a bother. Have spent some time trying this out and of course my
lack of knowledge doesnt help at all ! Can I ask you what I am sure is a
stupid question (for those that already know the answer), Do I make the
offset cell a fill colour on my worksheet or do I enter some formula that the
vba picks up on.

The other thing is that the sheets which are created are made up of a
template.
I have 2 sections to my set up sheet. One section is a list of up to 5
judges. One is a list of up to five competitors.

I create the template from the number of judges on the list, by copying a
single sheet verticallly as many times as there are judges. This works on an
'If Is Not Empty' macro, based on their being a name in the judges cell.
Once that template is produced, the list of competitors takes over and for
every cell with a competitors name in it the template becomes a named sheet
for that competitor. These are the sheet tabs I want to colour code. I
have been trying to fit in your coding to that scenario and I am afraid I am
struggling. I am attaching below the code I am using. I would be very
grateful if you could help. I am sorry it is so long, and probably could
have been 6 lines, if I knew what they were.



Sub PopulateSheets()

'in module 3'


Sheets("SET UP").Activate

Const Sheet1 = "Template"
Const Sheet2 = "JudgesTemplate"
Const Sheet41 = "SET UP"

Set JudgesCell1 = Sheets("SET UP").Range("B9")
Set JudgesCell2 = Sheets("SET UP").Range("B10")
Set JudgesCell3 = Sheets("SET UP").Range("B11")
Set JudgesCell4 = Sheets("SET UP").Range("B12")
Set JudgesCell5 = Sheets("SET UP").Range("B13")

'this copies a single sheet vertically to produce the judges score sheet
template.'
'The original sheet called Template is hidden'
'This makes the copy and puts the judges name in C2/35 etc'

If Not IsEmpty(JudgesCell1) Then

Worksheets("Template").Activate
Worksheets("Template").Range("A1:H33").Copy
Worksheets("JudgesTemplate").Activate
Worksheets("JudgesTemplate").Range("A1").Activate
ActiveSheet.Paste
ActiveSheet.Range("C2").Value = JudgesCell1

If Not IsEmpty(JudgesCell2) Then

Worksheets("Template").Activate
Worksheets("Template").Range("A1:H33").Copy
Worksheets("JudgesTemplate").Activate
Worksheets("JudgesTemplate").Range("A34").Activate
ActiveSheet.Paste
ActiveSheet.Range("C35").Value = JudgesCell2

If Not IsEmpty(JudgesCell3) Then

Worksheets("Template").Activate
Worksheets("Template").Range("A1:H33").Copy
Worksheets("JudgesTemplate").Activate
Worksheets("JudgesTemplate").Range("A67").Activate
ActiveSheet.Paste
ActiveSheet.Range("C68").Value = JudgesCell3

If Not IsEmpty(JudgesCell4) Then

Worksheets("Template").Activate
Worksheets("Template").Range("A1:H33").Copy
Worksheets("JudgesTemplate").Activate
Worksheets("JudgesTemplate").Range("A100").Activate
ActiveSheet.Paste
ActiveSheet.Range("C101").Value = JudgesCell4

If Not IsEmpty(JudgesCell5) Then

Worksheets("Template").Activate
Worksheets("Template").Range("A1:H33").Copy
Worksheets("JudgesTemplate").Activate
Worksheets("JudgesTemplate").Range("A133").Activate
ActiveSheet.Paste
ActiveSheet.Range("C134").Value = JudgesCell5

End If
End If
End If
End If
End If

'This then tests how many competitors cells have a name in the cell
'and produces a wksheet for each, naming the sheet tab'

Set CompetitorsRange = Sheets("SET UP").Range("B16:B20")

For Each cell In CompetitorsRange

If Not IsEmpty(cell) Then

Worksheets("JudgesTemplate").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell

'This is where I was trying to add your coding to say '
'(ActiveSheet.tab.colourindex = 8 ), but it wasn't having it'

'This puts the competitors names on each judges '
'section of the score sheet .

If Not IsEmpty(JudgesCell1) Then
ActiveSheet.Range("C1").Value = cell

If Not IsEmpty(JudgesCell2) Then
ActiveSheet.Range("C34").Value = cell

If Not IsEmpty(JudgesCell3) Then
ActiveSheet.Range("C67").Value = cell

If Not IsEmpty(JudgesCell4) Then
ActiveSheet.Range("C100").Value = cell

If Not IsEmpty(JudgesCell5) Then
ActiveSheet.Range("C133").Value = cell


End If
End If
End If
End If
End If
End If
Next cell
'This hides the Judges template after everything is set up,in theory
'but I cant get that far in reality!!!'

Worksheets("JudgesTemplate").Visible = False

End Sub

 

[Bob Phillips]

It would seem to me far simpler if you filled in the colours in the cells
adjacent to the names, in column C, and picked that up.

I have amended you code to do so, and also changed the logic after that as
it seems overly-busy

Set CompetitorsRange = Sheets("SET UP").Range("B16:B20")

For Each cell In CompetitorsRange

If Not IsEmpty(cell) Then

Worksheets("JudgesTemplate").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
ActiveSheet.Tab.ColorIndex = cell.Offset(0, 1).Interior.ColorIndex
ActiveSheet.Range("C133").Value = cell
End If
Next cell

Worksheets("JudgesTemplate").Visible = False

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SYBS]

Thank you so much, it works perfectly and I now see how it can be done for
the future. I really am very grateful, it was beginning to turn an enjoyable
challenge into a nightmare !

Thanks again

Sybs