Solved

2 Pivot Tables in VBA

Posted on 2010-08-25
4
368 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
Comment Utility
What line do you get the error on?
0
 
LVL 85

Accepted Solution

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


0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
Also, can you post your workbook?
0
 

Author Closing Comment

by:ScuzzyJo
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

10 Experts available now in Live!

Get 1:1 Help Now