Solved

2 Pivot Tables in VBA

Posted on 2010-08-25
4
371 Views
Last Modified: 2012-05-10
Hi
I have a macro which, among other things, creates 2 pivot tables.  The source data for each is on different tabs and the resulting tables should be on different tabs.  I cheated and used the macro recorder to get the code, then copied it with some changes for the second one.

The first one works fine, but the second one gives "Run-time error: 5.  Invalid procedure call or argument".  I'm not sure why.  I thought, at first, that it might be that, having copied the code, it was trying to call both of them PivotTable1, so I changed the second to PivotTable2.  Everything else is the same other than my changing where the source data is and where the table destination is.

I'm awarding 500 points for a quick solution.

Thanks
Sarah

Sub CF_PivotTable()



'   CREATE A PIVOT TABLE FROM THE NON DISCOUNT INVOICES







    Sheets.Add

    ActiveSheet.Name = "Summary"

    

    Sheets("Summary list - all open items b").Select

    Range("A1").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 0).Select

    lRow = ActiveCell.Row



    

    Range("A1").Select

    Range(Selection, Selection.End(xlDown)).Select

    Range(Selection, Selection.End(xlToRight)).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "Summary list - all open items b!R1C1:R" & lRow & "C11", Version:= _

        xlPivotTableVersion10).CreatePivotTable TableDestination:="Summary!R1C1", _

        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

    Sheets("Summary").Select

    Cells(1, 1).Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Net due dt")

        .Orientation = xlRowField

        .Position = 1

    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Amount in local cur."), _

        "Sum of Amount in local cur.", xlSum

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveWorkbook.ShowPivotTableFieldList = False

    ActiveCell.Offset(0, 1).Columns("A:C").EntireColumn.Select

    Selection.Style = "Comma"

    ActiveCell.Offset(1, 3).Range("A1").Select

    

        Sheets.Add



    

'   CREATE A PIVOT TABLE FROM THE DISCOUNT INVOICES





    ActiveSheet.Name = "Discount Summary"

    

    Sheets("Discount list - all open items").Select

    Rows("1:1").Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("A1").Select

    Sheets("Summary list - all open items b").Select

    Rows("1:1").Select

    Selection.Copy

    Sheets("Discount list - all open items").Select

    ActiveSheet.Paste

    Range("A1").Select

    

    

    Sheets("Discount list - all open items").Select

    Range("A1").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 0).Select

    lRow = ActiveCell.Row



    

    Range("A1").Select

    Range(Selection, Selection.End(xlDown)).Select

    Range(Selection, Selection.End(xlToRight)).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "Discount list - all open items!R1C1:R" & lRow & "C11", Version:= _

        xlPivotTableVersion10).CreatePivotTable TableDestination:="Discount Summary!R1C1", _

        TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

        

   

    Sheets("Discount Summary").Select

    Cells(1, 1).Select

    ActiveWorkbook.ShowPivotTableFieldList = True

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Net due dt")

        .Orientation = xlRowField

        .Position = 1

    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Amount in local cur."), _

        "Sum of Amount in local cur.", xlSum

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveWorkbook.ShowPivotTableFieldList = False

    Columns("B:D").Select

    Selection.Style = "Comma"

    Range("E3").Select

    





End Sub

Open in new window

0
Comment
Question by:ScuzzyJo
  • 2
4 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33522267
What line do you get the error on?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33522318
On line 74 change the TableDestination argument to:
TableDestination:="'Discount Summary'!R1C1"


0
 
LVL 24

Expert Comment

by:broomee9
ID: 33522321
Also, can you post your workbook?
0
 

Author Closing Comment

by:ScuzzyJo
ID: 33522535
Hi
Rory - thanks, it's working now....:-)

Broomee9 - sorry, Rory beat you to it.  I couldn't post the workbook anyway as it has confidential info in it, but thanks for trying.

Sarah
:-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now