none
Creating a Pivot Table using Macro's

    Question

  • I am trying to create a macro that will enable me to generate a pivot table from a set of data that will be exported from our management system in .csv format. The data set is variable in size depending on montly activity. How can I get the macro to recogize that I want it to use all of the data on the sheet. When I use "record" to create the macro it uses absolutes to define the range (see below). How can I modify this code so that the pivot table will generate based on all available data on the sheet. Thank you!

     

    Code Block

    Sub TESTMACRO()
    '
    ' TESTMACRO Macro
    ' Macro recorded 10/29/2007 by dvassallo
    '

    '
        Rows("1:3").Select
        Range("A3").Activate
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "ALOG!R1C1:R17024C15").CreatePivotTable TableDestination:="", TableName:= _
            "PivotTable1", DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Action", _
            ColumnFields:="EnteredBy"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Action").Orientation = _
            xlDataField
        ActiveWindow.SmallScroll Down:=-144
    End Sub

     

     

     

     

    Monday, October 29, 2007 7:24 PM

Answers

  • The line it's stoping at is a continuation of the ActiveWorkbook.PivotCaches.Add command. As far as VBA is concerned those 4 lines are all one command, so the error could be anywhere in those 4 lines of code, not necessarily the DefaultVersion part of it.

     

    I'm at a loss as to what causes it, though. I have just copied and pasted your code into a new workbook, created a dummy set of data on a sheet called "ALOG", with column headings of Action and EnteredBy, and the code runs without errors.

     

    The first bit of your macro deletes three rows from whichever sheet is active when the macro is run. I assume that's intentional, but you're not deleting the header rows from sheet ALOG, are you (the row containing "Action" and "EnteredBy")? I tried that, and got a different error on a different line, so I doubt that's it.

     

    The error is still "type mismatch", isn't it?

    As I said before "Type mismatch" errors occur when it is given an unexpected data type, and it can also give this error when it's given a variable that's empty or an empty string ("") when it needs a value. You're not using variables, and the only data type that we've changed since your original recorded version is the range. If the CurrentRegion.Select is selecting the right data, then that range should be fine to pass to the PivotCaches.Add command.

     

    Try replacing "Worksheets("ALOG").Range("A1").CurrentRegion" in the problem line with "Selection", and see if that makes any difference (like below):

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Selection).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10

    That shouldn't make any difference, but it's worth a try anyway.

     

    And another possibility - we can pass the address of the range as a string instead of passing the range itself. Try this (new/changed code in blue):

    Code Block

    Sub TEST2()
    '
    ' TEST2 Macro
    ' Macro recorded 11/1/2007 by dvassallo
    '

    '
        Dim sRange As String

        Rows("1:3").Select
        Range("A3").Activate
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        Worksheets("ALOG").Activate
        Range("A1").CurrentRegion.Select
        sRange = "ALOG!" & Range("A1").CurrentRegion.Address

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        sRange).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable1").AddFields _
            RowFields:="Action", ColumnFields:="EnteredBy"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Action") _
            .Orientation = xlDataField
    End Sub

     

     

    Again, that shouldn't make any difference, because that parameter should accept either a range or a string.

    Wednesday, November 14, 2007 10:46 PM

All replies

  • You can use the .CurrentRegion property to return a range that contains the whole data "block". Try replacing the CreatePivotTable line with this one:

    Code Block

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Worksheets("ALOG").Range("A1").CurrentRegion).CreatePivotTable _

        TableDestination:="", TableName:= "PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10

     

    Thursday, November 01, 2007 8:28 AM
  • Thank you for your response. I've replaced the section you referenced in your reply with the code you provided and now I am getting a runtime error '13' for type mismatch. Any ideas? I have no idea what might be causing this error. Here is the modified code below:

     

    Code Block

    Sub TEST2()
    '
    ' TEST2 Macro
    ' Macro recorded 11/1/2007 by dvassallo
    '

    '
        Rows("1:3").Select
        Range("A3").Activate
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Worksheets("ALOG").Range("A1").CurrentRegion).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Action", _
            ColumnFields:="EnteredBy"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Action").Orientation = _
            xlDataField
    End Sub

     

     


    Thanks for your help!

    Thursday, November 01, 2007 6:31 PM
  • That works fine for me in Excel 2003. I doubt that it matters in this case, but what version are you using?

     

    The first thing to check - the code bases the "CurrentRegion" on cell A1 of a sheet called "ALOG". Is that cell part of the data "block" that you want? If not, change the sheet and/or cell reference to a cell that is in the data block (anywhere in the data will do, does not need to be the first cell). I don't think that's the problem, though - if I make it point to an empty cell I get a different error than you (a quite specific one saying there must be at least 2 rows of data).

     

    Could you please confirm which line is throwing the error (ie when you get the error, if you hit the Debug button, which line is highlighted)? Let us know what line it stops on, and exactly what the error says.

     

    NB "Type Mismatch" usually means you've got an invalid data type being used for something - eg you try to use a method that expects a range and you give it a text string instead. The only change to your original code was to swap a text value representing the source range for a VBA Range - these are different data types (your original was a String, my mod is a Range) and normally these could not be used interchangeably. However, the PivotTable.Add method (in Excel 2003, anyway) will accept SourceData as either a Range or a text string.

     

    If your error is occurring on the PivotCache.Add line, then position your cursor on the word Add in your code and hit [F1]. Scroll down until you can see "as it applies to the PivotCaches object", and read the section on the SourceData parameter. Does it say that it will accept a range or a text string, or does it only say text string?

     

    Assuming that it does say a range or string, something else to try is to add the following lines before the PivotCache.Add line:

    Code Block

    Worksheets("ALOG").Activate

    Range("A1").CurrentRegion.Select

    Step through the macro (instructions for that are below if you don't know how) and watch what it does when it runs those lines. Does it select the right data block, or does that throw an error?

     

    In case you don't already know about it, a useful tool for debugging code is to step through the macro and watch what it does at each step: 

    • To start stepping through, from Excel go Tools, Macros, choose the macro, and Step Into. Or, if you already have the VB editor open, you can either go Debug - Step Into or just hit [F8].
    • This will highlight the first line of code only (or it might give a Compile error).
    • To run the line, hit the [F8] key. The highlight will move to the next line (note comments get skipped).
    • Keep stepping through in this manner. If you get an error, the line that is highlighted is the problem line. You get the chance to change it and keep going (some changes stop debug mode, though).
    • You can swap back and forth between Excel and the VB editor while you're stepping through, but if you change sheets, or change the active cell in Excel then you should change back to the sheet/cell it was on before you continue stepping through.
    Friday, November 02, 2007 1:13 AM
  • Thank you again for your response. Okay, I used "Step Into" to watch how my macro effects the spreadsheet. As I press "F8," each line executes as expected right through the point where we used .CurrentRegion to select the cell range to be used in the Pivot Table. I get my error when I get to:

     

      

    Code Block
    DefaultVersion:=xlPivotTableVersion10

     

     

    Do you know why this might be happening? I am using Excel 2003. Thank you again for your help.
    Tuesday, November 13, 2007 8:45 PM
  • For your reference here is the code I am using in its entirety:

     

    Code Block

    Sub TEST2()
    '
    ' TEST2 Macro
    ' Macro recorded 11/1/2007 by dvassallo
    '

    '
        Rows("1:3").Select
        Range("A3").Activate
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        Worksheets("ALOG").Activate
        Range("A1").CurrentRegion.Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Worksheets("ALOG").Range("A1").CurrentRegion).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Action", _
            ColumnFields:="EnteredBy"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Action").Orientation = _
            xlDataField
    End Sub

     

     

    Tuesday, November 13, 2007 8:48 PM
  • The line it's stoping at is a continuation of the ActiveWorkbook.PivotCaches.Add command. As far as VBA is concerned those 4 lines are all one command, so the error could be anywhere in those 4 lines of code, not necessarily the DefaultVersion part of it.

     

    I'm at a loss as to what causes it, though. I have just copied and pasted your code into a new workbook, created a dummy set of data on a sheet called "ALOG", with column headings of Action and EnteredBy, and the code runs without errors.

     

    The first bit of your macro deletes three rows from whichever sheet is active when the macro is run. I assume that's intentional, but you're not deleting the header rows from sheet ALOG, are you (the row containing "Action" and "EnteredBy")? I tried that, and got a different error on a different line, so I doubt that's it.

     

    The error is still "type mismatch", isn't it?

    As I said before "Type mismatch" errors occur when it is given an unexpected data type, and it can also give this error when it's given a variable that's empty or an empty string ("") when it needs a value. You're not using variables, and the only data type that we've changed since your original recorded version is the range. If the CurrentRegion.Select is selecting the right data, then that range should be fine to pass to the PivotCaches.Add command.

     

    Try replacing "Worksheets("ALOG").Range("A1").CurrentRegion" in the problem line with "Selection", and see if that makes any difference (like below):

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Selection).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10

    That shouldn't make any difference, but it's worth a try anyway.

     

    And another possibility - we can pass the address of the range as a string instead of passing the range itself. Try this (new/changed code in blue):

    Code Block

    Sub TEST2()
    '
    ' TEST2 Macro
    ' Macro recorded 11/1/2007 by dvassallo
    '

    '
        Dim sRange As String

        Rows("1:3").Select
        Range("A3").Activate
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        Worksheets("ALOG").Activate
        Range("A1").CurrentRegion.Select
        sRange = "ALOG!" & Range("A1").CurrentRegion.Address

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        sRange).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable1").AddFields _
            RowFields:="Action", ColumnFields:="EnteredBy"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Action") _
            .Orientation = xlDataField
    End Sub

     

     

    Again, that shouldn't make any difference, because that parameter should accept either a range or a string.

    Wednesday, November 14, 2007 10:46 PM
  • That worked! Now I'm going to go back and pick through the code and try and figure out why I had so much difficulty with the original. Thank you so much.

    Wednesday, November 21, 2007 4:58 PM
  • HamptonsKid - Coincidentally I just hit the same problem.  Passing a Range obj in the PC.Add method will fail, but using a string version of the range works fine.  I'm using source data from someone else...i've never had problems with my own data and have been working with VBA & pivot tables for years.

    I'm at a point where I think the Range obj may be 'corrupt' due to data in the range.

    My reasoning:
    I'm at a point where the range object is OK when defined up to a certain row (ex: A1:Z6000 is OK), but fails when i extend to include the next row (ex: A1:Z6001).  If i copy row 6000 and insert it between 6000 and 6001, then A1:Z6001 will work and A1:Z6002 will fail.

    Very strange indeed - i would't think that the data content of the range would affect the range obj itself.  I'm still debugging, will add to this thread if I find the root cause.

    [EDIT - UPDATE] - The problem occurs because the Range object includes one or more cells with >255characters.  Can anyone explain why a Range obj containing at least 1 cell with >255chars causes a problem when used in the PC.Add method?  I'm assuming its not a known issue (if it is, please link KB article #).  I have O2K3 like the OP.

    1.) The PivotCache.Add can propely utilize a source table of cells with >255char (proven when using a string input for the range)
    2.) The Range object appears OK if it contains one or more cells with >255chars.  Can get row & col count, etc...from obj.


    [EDIT - UPDATE 2] - This also fails w/ Excel 2007.
    Friday, December 14, 2007 5:38 PM
  • I have also received the type mismatch error, but found that the pivot table is still created.  Once I call the add method and trap error 13, I am able to continue building the pivot table.

     

    Monday, February 18, 2008 8:18 PM