Excel TableDestination As String?
Try the following.
Dim strDestWB As String
Dim strSourceWB As String
'Edit the following 2 lines to match your workbook names.
'Note that the workbooks must be saved and must be open
'before the code will work.
strSourceWB = "Book1Source.xls"
strDestWB = "Book2pivot.xls"
Workbooks(strDestWB).PivotCaches.Add(SourceType:=x lDatabase, _
SourceData:="[" & strSourceWB & "]" & "YIS!R2C1:R15C26") _
..CreatePivotTable TableDestination:= _
"[" & strDestWB & "]" & "YIS!R18C1", _
TableName:=PivotTable15, _
DefaultVersion:=xlPivotTableVersion10
--
Regards,
OssieMac
"gstoa" wrote:
I've recorded an Excel macro which successfully creates a new pivot
table on mySheet.xls. For example:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="YIS!R2C1:R15C26").CreatePivotTable
TableDestination:="[mySheet.xls] YIS!R18C1", TableName:=PivotTable15,
DefaultVersion:=xlPivotTableVersion10
I need to be able to dynamically change the TableDestination value
from mySheet.xls to a string variable as my macro will be creating
pivot tables across multiple workbooks.
I've tried turning this into one big string using Chr$(34) for the
double quotes but can't seem to get this working correctly. Any
suggestions are welcomed!
|