Two pivot table from different sheets

[Giriraj Desai]

Hi,

Could you please help me , I have recorded a macros to get two different pivot table from two different sheets and the pivot is made in one single sheet.

when I run the macros the first pivot is getting correctly for the second one I am getting a run time error 5 "invalid argument". i have tried could not able to achieve it

can you please help me and below is the recorded syntex.

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("A:K").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'sap Vat report - Copy'!A:K", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DocumentNo")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(" Tax base amount"), _
"Count of Tax base amount", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Tax base amount")
.Caption = "Sum of Tax base amount"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("Rave report").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

""This is part where I am getting error"
Run time error 5 "Invalid procedure call argument".

----------------------------------------------------------------------

Columns("B:X").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Rave report'!B:X", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="'[Pivot 2.xlsx]Sheet1'!R3C9", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion12

-------------------------------------------------------------------------------------


ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("PO/BILLING")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("INVOICE VALUE"), "Count of INVOICE VALUE", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of INVOICE VALUE")
.Caption = "Sum of INVOICE VALUE"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E1").Select
End Sub

Many thanks in advance for your help!!!

Regards
Giriraj

 

[NARAYANK991]

Hi Desai ,

There are several possibilities ; it would make things easier for everyone if you could upload your workbook.

1. The Excel help on this says :

The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression.

Only you can say whether this applies in your case or not.

2. The following link has a similar problem relating to the name of the worksheet , and how finally it was resolved ; you can see if it applies to your workbook name :

http://www.mrexcel.com/forum/excel-questions/603521-runtime-error-5-recorded-macro.html

3. There is some discussion on this error here :

http://social.msdn.microsoft.com/Fo...runtime-error-5-running-microsoft-excel-macro

Narayan

 

[Giriraj Desai]

Hi Narayan,

Thanks for the reply!!!

I tried to attach the file I got error as it is big file ,can you pls give me your mail ID I can send across to you

or is there any to upload can you pls let me know

 

[NARAYANK991]

Hi Desai ,

This forum can accept files only up to 1 MB in size ; any file larger than that will not be accepted.

If you want , you can upload to any public file-sharing website such as GoogleDocs , DropBox , HotFile , RapidShare , SpeedyShare ,... give others permission to access the file , and then post the access link here in this topic.

If your file contains confidential data , you can email me the file at narayank1026 [at] gmail [dot] com.

Narayan

 

[Giriraj Desai]

Hi Narayan,

yes this is a confidential data ,I am mailing to your E mail ID -thanks.