Worksheet name in cell

[Graham H]

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.Valuerng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If

 

[Bob Phillips]

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

 

[Barb Reinhardt]

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

 

[Barb Reinhardt]

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

Barb Reinhardt

 

[Graham H]

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

Graham

 

[Bob Phillips]

Are you sorted?

 

[Graham H]

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