Worksheet name syntax question

  • Thread starter Thread starter anny
  • Start date Start date
A

anny

greetings

I have code that generates names (eg T2_Master)
Dim TaskWSName as String
TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"

Later, I want to have code that refers to Worksheets having these names.
Here's an eg:
Worksheets("PrintReport").Range("A1").Formula =
"=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0))


How do I refer to T2_Master! in the INDEX or the MATCH statements? Using
TaskWSName! doesn't work.

Thanks in advance
anny
 
I would let excel do the work for me:

dim ColE as range
dim ColA as range
dim TaskWSName as String

'watch out for "E8" <--with double quotes
TaskWSName = "T" & ActiveSheet.Range("E8").value & "_Master"

with worksheets(taskwsname)
set cole=.range("e:E")
set colA = .range("a:a")
end with

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & cole.address(external:=true) & ",match(a8," & _
cola.address(external:=true) & ",0))"


======
You could build the strings yourself,

Worksheets("PrintReport").Range("A1").Formula _
= "=index('" & TaskWSName & "'!a:a,match(a8," & _
"'" & TaskWSName & "'!a:a,0))"

===
You could drop the $ signs by using:

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & ColE.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",match(a8," & _
ColA.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",0))"
 
Dave : I'm a bit puzzled about your use of apostrophes with the quotation
marks (in 4 places)
You could build the strings yourself,

Worksheets("PrintReport").Range("A1").Formula _
= "=index('" & TaskWSName & "'!a:a,match(a8," & _
"'" & TaskWSName & "'!a:a,0))"
Click to expand...

Why can't I just do the following? It seems to work OK. Do I really need
the aposstrophes?

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & TaskWSName & "!a:a,match(a8," & _
TaskWSName & "!a:a,0))"

Thanks again
anny
 
It depends on what's in TaskWSName.

Try a simple test.

Create a workbook with two sheets. Name them A and B.
In A1 of worksheet A, put this formula:
=b!a1

Now rename worksheet B to "this is worksheet B"
and take a look at your formula.

So if you always know that the sheetname will be T2_Master, those apostrophes
aren't required. But they don't hurt.

And if the sheet name changes to where the apostrophes are required, you have to
have them (well, that's what required means <bg>.)

So I think it's a good thing to add them all the time. It saves wear and tear
when something else changes.
 
Back
Top