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

The syntax is

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

Mike

"SYBS" wrote:

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

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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

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" wrote in message
...
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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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

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

Thanks

Sybs


"Bob Phillips" wrote:

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" wrote in message
...
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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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

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").Activat e
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").Activat e
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" wrote:

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" wrote in message
...
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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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

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" wrote in message
...
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").Activat e
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").Activat e
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" wrote:

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" wrote in message
...
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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200713


"SYBS" wrote:

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