[RESOLVED] Problem with VB Macro in Excel for Pivot Table

Hi

I am generating a pivot table out of worksheet named: Reporting

I recorded an excel macro of the steps to produce the pivot table:

VB Code:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "Reporting!R1C1:R378C31"
    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("Severity", _
        "Open/Closed"), ColumnFields:="Area"

Because the range of cells changes I have produced theses variables:

LastRow
LastCol

I am trying to use those in the code above such that:

VB Code:

LastRow = ":R" & LastRow
 
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=“Reporting!R1C2” & LastRow + “C” + LastCol
    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("Severity", _
        "Open/Closed"), ColumnFields:="Area"

When in this format the code doesn't work.

Cheers

Dave

This is not Visual Basic, but Excel-VBA. These are not the same. Please ask at the proper place.. ;)

Excel VBA question moved to Office Development

You need to add a full-colon : between the row and column references

VB Code:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=“Reporting!R1C2” & LastRow + “[B]:[/B]C” + LastCol

Tried this but when I run the macro the

:C

part of the code is highlighted and an error "Sub or Function not Defined"


ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=“Reporting!R1C2” & LastRow + “:C” + LastCol

OK, lets take a step back.

Using the wizard in your code is probably not the best idea.

Do you need to create a new PT every time the code is run, or do you just need to update the source range for an existing PT?

The wizard was in there because that was the route through which I went whilst the Macro was recording.

A new PT is created every time the macro is re-run and it is likely that the range of the spreadsheet will change.

Is it a requirement that a new table is created, or is it just because that is the way the macro was recorded?

It is a requirement because every time the macro is run it will have new source data therefore the table will be different

As long as the Fields (Columns) are staying the same and it is just the Data (Rows) that are changing, then there is no reason to creat a new PT, you can just change the source range of your existing table and refresh the pivot cache.

How can the source range be set automatically every time the macro is run?

OK
In the following code I have made some assumptions. that you may need to change, apart from these the code will show you how to change the source data and refresh your existing PT.

Assumptions
1/ You data is contained in a worksheet called "Reporting"
2/ Your data in the "reporting" worksheet starts in cell A1
3/ There is nothing else in the "reporting" worksheet, i.e no totals, no analysis below or to the right of the datatable.
4/ Your pivot table is in a worksheet called "PivotSheet" (you will definetly need to change this one)...
5/ It is the only PT on that sheet

VB Code:

Sub UpdatePivot()
Dim MyPivot As PivotTable
Dim rngSource As Range
    
    'Get a reference to the Pivot Table
    Set MyPivot = ThisWorkbook.Worksheets("PivotSheet").PivotTables(1)
    
    'Get a reference to the source data table
    Set rngSource = ThisWorkbook.Worksheets("Reporting").UsedRange
    
    With MyPivot
        
        'Update the Source data of the PT
        .SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)
        
        'Refresh the dat in the PT
        .RefreshTable
        
    End With
    
    'Clear Object Variables
    Set rngSource = Nothing
    Set MyPivot = Nothing
End Sub

Run time error:1004

The pivot table field name is not valid. To create a pivot table you must use data that is organised as a list with labelled columns.

Are all of my assumptions valid?

2/ Your data in the "reporting" worksheet starts in cell A1
A1 is colum header name - i thought this was needed?

5/ It is the only PT on that sheet
- there are more than 1 PT one that sheet. The PT is number 3 so I set as follows:

Set MyPivot = ThisWorkbook.Worksheets("PivotSheet").PivotTables(3)

Sorry, when I said data I was including the Column Headers, so yes you do need the headers in Row 1.
Can you upload the worbook or is it confidential?

My best guess right now is that the problem is with the UsedRange property of the worksheet.
Can you try the following. Its the same code, but instead of using the UsedRange property, I want you to insert the code you previously used to determine the Last Row and Column numbers.

VB Code:

Sub UpdatePivot()
Dim LastRow As Long
Dim LastCol As Long
Dim MyPivot As PivotTable
Dim rngSource As Range
    
    'Get a reference to the Pivot Table
    Set MyPivot = ThisWorkbook.Worksheets("PivotSheet").PivotTables(1)
    
    'Insert whatever code you were using to get
    'the last row and column numbers here
    
    
    'Get a reference to the source data table
    With ThisWorkbook.Worksheets("Reporting")
        Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
    End With
    With MyPivot
        
        'Update the Source data of the PT
        .SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)
        
        'Refresh the dat in the PT
        .RefreshTable
        
    End With
    
    'Clear Object Variables
    Set rngSource = Nothing
    Set MyPivot = Nothing
End Sub

Sub MacroMainPivot()

Dim LastRow As Long
Dim LastCol As Long
Dim MyPivot As PivotTable
Dim rngSource As Range

'Get a reference to the Pivot Table
Set MyPivot = ThisWorkbook.Worksheets("Statistical analysis").PivotTables(3)

'Insert whatever code you were using to get
'the last row and column numbers here

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
MsgBox "" & LastRow
' Find the last real column

LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
MsgBox "" & LastCol
End With

'Get a reference to the source data table
With ThisWorkbook.Worksheets("Reporting")
Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
With MyPivot

'Update the Source data of the PT
.SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)

'Refresh the dat in the PT
.RefreshTable

End With

'Clear Object Variables
Set rngSource = Nothing
Set MyPivot = Nothing
End Sub

Code above executes but

the table doesn't update

Hold on that may be it,

refresh button was not pressed,

Ill get back to you

That works , awesome help,

can I leave feedback in this forum

Just glad I could help.
Don't forget to mark this thread as resolved (its under Thread Tools at the top of the page)