Pivot table in new Worksheet

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
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.

Rich (BB code):
    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?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,370
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You need something like:
Rich (BB code):
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This:

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

should be:

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

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
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....
 

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
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.
 

Forum statistics

Threads
1,082,478
Messages
5,365,793
Members
400,854
Latest member
zenith_11

Some videos you may like

This Week's Hot Topics

Top