Referencing a variable in place of worksheet name

G'Day everyone

Trying to create a Pivot on a worksheet that was created for a variable entered by user. But i am having trouble getting Excel to accept the variable as the worksheet name. (below in Red) Any ideas??

Please Login or Register  to view this content.

Could the problem be this?

You have this line of code
NewSheet1.Name = outputvar1

but I think you need this instead

outputvar1 = NewSheet1.Name

Originally Posted by Mallycat

Could the problem be this?

You have this line of code
NewSheet1.Name = outputvar1

but I think you need this instead

outputvar1 = NewSheet1.Name

This is not the problem, "outputvar1" has been given a value earlier in the code.

using "NewSheet1.Name = outputvar1" sets the new sheets name to be that of the value set for "outputvar1"

I can reference the variable "outputvar1" successfully when naming the newly created worksheet and setting the value of cell D1 - But i cannot use it to identify the worksheet in VBA code again. Why is this??

Looks OK to me. I have tested it and it works find.

Try putting a break in the code just before this line, and seeh what the variable evalutes to

Please Login or Register  to view this content.

^^ This works fine

Please Login or Register  to view this content.

^^ so does this (where the sheet is referenced as a fixed name (100241)

Please Login or Register  to view this content.

BUT it is really important that I am able to reference the sheet as a variable as i am dynamically creating up to 10 sheets which could have any different cost centre number (which means i cant hard code the sheet number as above)

Since i create the pivots on each sheet as i go, is there any way to replace referncing the sheet by it name by using some other type such as "active sheet??"

Originally Posted by protonLeah

I think that the line:

"'[Overheads.Engine.xls]outputvar1'!R5C1", TableName:="PivotTable1",...

where you have outputvar1 embedded in double quotes as a text string, should be:

"'[Overheads.Engine.xls]" & outputvar1 & "'!R5C1", TableName:="PivotTable1",...

Mate your a bloody legend!!! That does the trick!!