Worksheet name in cell
 

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

Worksheet name in cell
 

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

Worksheet name in cell
 

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

Worksheet name in cell
 

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

Worksheet name in cell
 

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

Worksheet name in cell
 

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

Worksheet name in cell
 

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