I have a procedure creating new worksheets and naming them according to a list.(Courtesy
of Debra Dalgleish's site) the relevant part of which is shown below.
What I am trying to do is to add a line where I have indicated to put the new sheet name
into cell Q1 in each new sheet. I am failing miserably in what I thought was very
straightforward. The sheet is not active so I cannot use Activesheet.name and attempts at
Range("Q1").value = c.Value fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
wsNew.Range("Q1").Value = wsNew.Name
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

--
__________________________________
HTH

Bob

"Graham H" wrote in message
...
I have a procedure creating new worksheets and naming them according to a
list.(Courtesy of Debra Dalgleish's site) the relevant part of which is
shown below.
What I am trying to do is to add a line where I have indicated to put the
new sheet name into cell Q1 in each new sheet. I am failing miserably in
what I thought was very straightforward. The sheet is not active so I
cannot use Activesheet.name and attempts at Range("Q1").value = c.Value
fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

How have you defined the range "c"? Add this to your code before defining
the WSname. Also, there are restrictions on what can go into a worksheet
name. Some characters aren't allowed.

debug.print c.parent.name, c.address, c.value

to see if there's anything in the range c
--
HTH,
Barb Reinhardt



"Graham H" wrote:

I have a procedure creating new worksheets and naming them according to a list.(Courtesy
of Debra Dalgleish's site) the relevant part of which is shown below.
What I am trying to do is to add a line where I have indicated to put the new sheet name
into cell Q1 in each new sheet. I am failing miserably in what I thought was very
straightforward. The sheet is not active so I cannot use Activesheet.name and attempts at
Range("Q1").value = c.Value fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

Oops, for some reason I overlooked the question you asked. DOH!

Barb Reinhardt



"Barb Reinhardt" wrote:

How have you defined the range "c"? Add this to your code before defining
the WSname. Also, there are restrictions on what can go into a worksheet
name. Some characters aren't allowed.

debug.print c.parent.name, c.address, c.value

to see if there's anything in the range c
--
HTH,
Barb Reinhardt



"Graham H" wrote:

I have a procedure creating new worksheets and naming them according to a list.(Courtesy
of Debra Dalgleish's site) the relevant part of which is shown below.
What I am trying to do is to add a line where I have indicated to put the new sheet name
into cell Q1 in each new sheet. I am failing miserably in what I thought was very
straightforward. The sheet is not active so I cannot use Activesheet.name and attempts at
Range("Q1").value = c.Value fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

Thanks to both for your help. It is much appreciated.

Graham

Barb Reinhardt wrote:
Oops, for some reason I overlooked the question you asked. DOH!

Barb Reinhardt



"Barb Reinhardt" wrote:

How have you defined the range "c"? Add this to your code before defining
the WSname. Also, there are restrictions on what can go into a worksheet
name. Some characters aren't allowed.

debug.print c.parent.name, c.address, c.value

to see if there's anything in the range c
--
HTH,
Barb Reinhardt



"Graham H" wrote:

I have a procedure creating new worksheets and naming them according to a list.(Courtesy
of Debra Dalgleish's site) the relevant part of which is shown below.
What I am trying to do is to add a line where I have indicated to put the new sheet name
into cell Q1 in each new sheet. I am failing miserably in what I thought was very
straightforward. The sheet is not active so I cannot use Activesheet.name and attempts at
Range("Q1").value = c.Value fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

Are you sorted?

--
__________________________________
HTH

Bob

"Graham H" wrote in message
...
Thanks to both for your help. It is much appreciated.

Graham

Barb Reinhardt wrote:
Oops, for some reason I overlooked the question you asked. DOH!

Barb Reinhardt



"Barb Reinhardt" wrote:

How have you defined the range "c"? Add this to your code before
defining the WSname. Also, there are restrictions on what can go into a
worksheet name. Some characters aren't allowed.
debug.print c.parent.name, c.address, c.value

to see if there's anything in the range c
--
HTH,
Barb Reinhardt



"Graham H" wrote:

I have a procedure creating new worksheets and naming them according to
a list.(Courtesy of Debra Dalgleish's site) the relevant part of which
is shown below.
What I am trying to do is to add a line where I have indicated to put
the new sheet name into cell Q1 in each new sheet. I am failing
miserably in what I thought was very straightforward. The sheet is not
active so I cannot use Activesheet.name and attempts at
Range("Q1").value = c.Value fail miserably. Any help much appreciated.

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

Bob Phillips wrote:
Are you sorted?

Yes Bob, many thanks for coming back. The problem really was with the rest of the code
which I didn't notice. The procedure created new worksheets and copied filtered data to
these. When it was re-run if the sheets already existed the procedure cleared all the
cells in the sheet and entered the filtered data. The bit of code I wanted altered was
when a new sheet was added and yes, you guessed it, I was trying all the permutations when
all the sheets were already there and consequently the code for this part was ignored! I
had taken Debras code for the whole thing and should have paid more attention. I am
grateful for you checking up.

Graham