Macro
 

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _TableName:="PivotTable2"

Error: Argument not optional.

I think this comes from the last section of the above TableDestination:=""

Help

Macro
 

1) Split the one-liner into several separate bits, and check where the error
occurs
2) Spend a few minutes reading the Excel VBA manual

/MP

"Nbiggss" wrote:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _TableName:="PivotTable2"

Error: Argument not optional.

I think this comes from the last section of the above TableDestination:=""

Help

Macro
 

Yes, I believe TableDestination is a required argument. So why not supply
one.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="'" & Activehseet.Name & "'!R21C2:R65536C8") _
.CreatePivotTable TableDestination:=Activesheet.Range("M1"), _
TableName:="PivotTable2"

--
Regards,
Tom Ogilvy



"Nbiggss" wrote:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _TableName:="PivotTable2"

Error: Argument not optional.

I think this comes from the last section of the above TableDestination:=""

Help

Macro
 

Yeah, and since I feel I was snubbing you a bit before (sorry) I'll provide
you with a slightly more elaborate reply this time.

The method is rather talkative and lengthy, but I've tried to be as clear as
possible.

HTH,
/MP

==================================================

Private Sub PivotTableTest()

On Error GoTo ErrorHandler

Dim oSourceSheet As Worksheet
Dim oTargetSheet As Worksheet

' TODO: Change the workbook names (enclosed in "")
' to the appropriate values you should be using.
Set oSourceSheet = Workbooks("MySourceDataSheet")
Set oTargetSheet = Workbooks("MyTargetDataSheet")

Dim oPC As PivotCache
Dim oPT As PivotTable

Set oPC = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="'" & oSourceSheet.Name & "'!R21C2:R65536C8")

' TODO: Change the range to point to the upper left
' cell of the destination range you want to use.
Set oPT = oPC.CreatePivotTable( _
TableDestination:=oTargetSheet.Range("A1"), _
TableName:="PivotTable2")

Exit Sub

ErrorHandler:
' Spit out some additional info about any error that may occur
MsgBox "Error (" & CStr(Err.Number) & ") : " & Err.Description

End Sub

================================================== =




"Tom Ogilvy" wrote:

Yes, I believe TableDestination is a required argument. So why not supply
one.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="'" & Activehseet.Name & "'!R21C2:R65536C8") _
.CreatePivotTable TableDestination:=Activesheet.Range("M1"), _
TableName:="PivotTable2"

--
Regards,
Tom Ogilvy



"Nbiggss" wrote:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _TableName:="PivotTable2"

Error: Argument not optional.

I think this comes from the last section of the above TableDestination:=""

Help

Macro
 

....And it'd be even better if my code was working (d'oh!)

Change the buggy lines:

Set oSourceSheet = Workbooks("MySourceDataSheet")
Set oTargetSheet = Workbooks("MyTargetDataSheet")

Into something more applicable:

Set oSourceSheet = Worksheets("MySourceDataSheet")
Set oTargetSheet = Worksheets("MyTargetDataSheet")

And another thing: do you really have data in the entire range
[R21C2:R65536C8] ? Shouldn't the range reflect the amount of data you've got?
In my test code I got an error when I didn't accurately set the data range,
i.e., when I the range included all the rows in the sheet.

Cheers,
/MP

"Mat P:son" wrote:

Yeah, and since I feel I was snubbing you a bit before (sorry) I'll provide
you with a slightly more elaborate reply this time.

The method is rather talkative and lengthy, but I've tried to be as clear as
possible.

HTH,
/MP

==================================================

Private Sub PivotTableTest()

On Error GoTo ErrorHandler

Dim oSourceSheet As Worksheet
Dim oTargetSheet As Worksheet

' TODO: Change the workbook names (enclosed in "")
' to the appropriate values you should be using.
Set oSourceSheet = Workbooks("MySourceDataSheet")
Set oTargetSheet = Workbooks("MyTargetDataSheet")

Dim oPC As PivotCache
Dim oPT As PivotTable

Set oPC = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="'" & oSourceSheet.Name & "'!R21C2:R65536C8")

' TODO: Change the range to point to the upper left
' cell of the destination range you want to use.
Set oPT = oPC.CreatePivotTable( _
TableDestination:=oTargetSheet.Range("A1"), _
TableName:="PivotTable2")

Exit Sub

ErrorHandler:
' Spit out some additional info about any error that may occur
MsgBox "Error (" & CStr(Err.Number) & ") : " & Err.Description

End Sub

================================================== =




"Tom Ogilvy" wrote:

Yes, I believe TableDestination is a required argument. So why not supply
one.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="'" & Activehseet.Name & "'!R21C2:R65536C8") _
.CreatePivotTable TableDestination:=Activesheet.Range("M1"), _
TableName:="PivotTable2"

--
Regards,
Tom Ogilvy



"Nbiggss" wrote:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _TableName:="PivotTable2"

Error: Argument not optional.

I think this comes from the last section of the above TableDestination:=""

Help