CODEWITHSUNDEEP
excelvbapivot-table
Jesse Smothermon
Jesse Smothermon

Reputation: 1051

VBA Excel Pivot Table

I recorded creating a pivot table for arbitrary data and it seems to work fine when given one set of data. However when the program is given multiple columns of data the output is incorrect. The code for a general pivot table is below. It is similar to a recorded pivot table except that the names and columns are less hard-coded

    Dim tempString As String

    tempString = ActiveWorkbook.Sheets(sheetName).Range(column & "1").Value

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        sheetName & "!R1C" & ColTextToInt(column) & ":R" & g_totalRow & "C" &     ColTextToInt(column) & "", Version:=xlPivotTableVersion12 _
        ).CreatePivotTable TableDestination:=ActiveSheet.Range(colNamePos & "1"), _
        TableName:=column & "Table", DefaultVersion:=xlPivotTableVersion12
    ActiveCell(1, ColTextToInt(colNamePos)).Select
    With ActiveSheet.PivotTables(column & "Table").PivotFields(tempString)
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.Range(colNamePos & "1").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables(column & "Table").AddDataField ActiveSheet.PivotTables( _
        column & "Table").PivotFields(tempString), "Count of " & tempString, xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False

The program messes up towards the end. Here is the specific line:

ActiveSheet.PivotTables(column & "Table").AddDataField ActiveSheet.PivotTables( _
        column & "Table").PivotFields(tempString), "Count of " & tempString, xlCount

When this line of code is hit the column of the pivot table (has specific cell names) gets covered up with the grand total number of how many total rows there are. Below is the key for what the variables stand for

sheetName = worksheet that has all raw data (ex. "Sheet1") column = User specifies a column that they want the program to look at (ex. "F") tempString = given the first two variables this will be equal to the title of the column (ex. "State") g_totalRow = how many rows are in the raw data sheet colNamePos = the column the program will input the pivot table into

Thank you,

Jesse Smothermon

Upvotes: 0

Views: 3303

Answers (1)

Jesse Smothermon
Jesse Smothermon

Reputation: 1051

Got it. The bit of code that was causing the error needs to be in front of the "with" portion. I'm not entirely sure why that is but I tried it and it worked. If anyone thinks they have a reason as to why it needed to be in front of the with I would like to know.

Code:

    tempString = ActiveWorkbook.Sheets(sheetName).Range(column & "1").Value

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        sheetName & "!R1C" & ColTextToInt(column) & ":R" & g_totalRow & "C" & ColTextToInt(column) & "", Version:=xlPivotTableVersion12 _
        ).CreatePivotTable TableDestination:=ActiveSheet.Range(colNamePos & "1"), _
        TableName:=column & "Table", DefaultVersion:=xlPivotTableVersion12
    ActiveCell(1, ColTextToInt(colNamePos)).Select
    ActiveSheet.PivotTables(column & "Table").AddDataField ActiveSheet.PivotTables( _
        column & "Table").PivotFields(tempString), "Count of " & tempString, xlCount
    With ActiveSheet.PivotTables(column & "Table").PivotFields(tempString)
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False

Thank you,

Jesse Smothermon

Upvotes: 1

Related Questions