Pivot table in new Worksheet

[Legacy 108787]

My VB code makes a new worksheet for every PT (or rather will in the near future). I'm trying to get that to work, but I ran into some problems with TableDestination.

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Data'!" & Rng2.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:= _
        "'[Master-File.xls]Analyse'&Nn&''!R9C1 TableName:="meldingen_sectie", DefaultVersion _
        :=xlPivotTableVersion10

The worksheet has a variable name, Analyse1, Analyse2, etc.
The code I pasted up there doesn't work, nor do double quotes or no quotes.

Is there a way to have a variable TableDestination in this expression?

 

[RoryA]

You need something like:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Data'!" & Rng2.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable _
TableDestination:="'[Master-File.xls]Analyse" & Nn & "'!R9C1", _
TableName:="meldingen_sectie", DefaultVersion:=xlPivotTableVersion10

 

[Andrew Poulsom]

This:

"'[Master-File.xls]Analyse'&Nn&''!R9C1

should be:

"'[Master-File.xls]Analyse" & Nn & "'!R9C1

 

[Legacy 108787]

That works indeed. I never knew spaces could make such a difference.

"'[Master-File.xls]Analyse" &Nn& "'!R9C1" throws an "Expected: End of Statement"
"'[Master-File.xls]Analyse" & Nn & "'!R9C1" works smoothly....

 

[Andrew Poulsom]

It wasn't just the spaces. It was also the misplacing of single and double quotes.

 

[Legacy 108787]

It wasn't just the spaces. It was also the misplacing of single and double quotes.

I have tried all possible combinations of quotes before I posted the question here, the final solution without spaces was actually my original try, so it does appear the spaces were the essence of my misfortune.