Excel 2010 VBA to refresh data range and refresh pivot table

[tsejoe]

Hi All,

I would like to set the VBA codes so that Excel will update the data range and refresh the pivot tables, everytime when the data is copied and paste into a worksheet. The data source worksheet is "CorpStoresSOH"; and the pivot table is in the "CorpStores SOH Summary". It is found that it has Run time 91 error.
can anyone advise what should be the right coding?


Worksheets("CorpStoresSOH").Activate
Range("A3").Select
lastrow = Worksheets("CorpStoresSOH").UsedRange.Rows.Count
Set MB58range = Worksheets("CorpStoresSOH").Range("A3:L" & lastrow)

Worksheets("CorpStoresSOH Summary").PivotTables("pivottable_2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MB58range, Version:=xlpivottableversion15)

Thank you so much~~

Best Regards,
joe

 

[jorismoerings]

Hi,

Would you consider to change the data sheet "CorpStoresSOH" to a dynamic excel table;
By changing the data source for the pivot-table to a dynamic excel table, Excel solved your problem for re-adjusting the data range.

To do that:

•Convert your dataset on sheet "SalesData" to a Table.
•Select the dataset range then through the Ribbon: Insert > Table
•In the Create Table dialog, check "My table has headers" > OK


Click on the Table, then in the Ribbon's "Design" tab, look to see what name Excel has automatically assigned the new Table.
Let's assume it's the first table in the workbook and it's named "Table1".

Go into each of your pivot-table's "Change data source" dialog and replace the current range address with: Table1

Remains the updating of the pivot-table, if i understood your question correctly it would be on a different worksheet but i'm unsure if it's just one or more pivot-tables

I’d suggest you use code that will be triggered each time you activate the worksheet containing the pivot table. To cover more than one pivot tables on the worksheet, the code will check if the worksheet contains one or more pivottables and refresh them all.

The code needs to be pasted into the ThisWorkbook code module of your workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)    
Dim pvt As PivotTable
        
If Sh.PivotTables.Count = 0 Then Exit Sub        
   Application.EnableEvents = False        
For Each pvt In Sh.PivotTables       
   pvt.RefreshTable    
Next pvt     

CleanUp:    
Application.EnableEvents = True 
End Sub

 

[tsejoe]

Hi There,

Thank you so much for the advice and suggestions. I think i won't change the datasheet to dynamic excel table. May i please ask any idea?

Thank you so much.

Best Regards,
Joe

 

[jorismoerings]

Give this code a try....

Sub AutoAdjustPivotDataRange()

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartC As Range
Dim HRange As Range
Dim PName As String
Dim UpdRange As String

'Set Variables
  Set Data_sht = ThisWorkbook.Worksheets("CorpstoresSOH")
  Set Pivot_sht = ThisWorkbook.Worksheets("CorpStoresSOH Summary")

'Pivot Table Name
  PName = "PivotTable_2"

'get data range
  Set StartC = Data_sht.Range("A3")
  Set HRange = Data_sht.Range(StartC, StartC.SpecialCells(xlLastCell))
  
  UpdRange = Data_sht.Name & "!" & HRange.Address(ReferenceStyle:=xlR1C1)

'Change Data Source
  Pivot_sht.PivotTables(PName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=UpdRange)
      
'Refresh Pivot
  Pivot_sht.PivotTables(PName).RefreshTable

End Sub

 

[tsejoe]

Hi,

Thank you so much for the advice, but now it gives me another prompt:
"Run time error: "-2147024809(80070057)'
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

But seems that the PivotTable field names and the data source are all match... Wonder what the prblem is?

Thank you so much.

Best Regards,
joe

 

[jorismoerings]

Hi Joe,

I triple checked the code I gave you and even did a sample with it. Worked fine. Unfortunately this error code is a tricky one and can be caused by several reasons which I'm not familiar with all of them.
The only way I could reproduce the error was by trying to use an invalid field header name (I blanked one of the headers). Make sure there are no invalid names on fields in the data range even for the ones you don't use in the pivot table report, beware of hidden columns.

So not a complete solution but a search direction.

 

[tsejoe]

Hi,

Thank you so much for the help - shall i use another approach, is to create a new pivot table and rename the worksheet so that other sheet can do a lookup against the refreshed pivot table? If that can be a feasible direction. Could you please kindly tell me the codes for that step please?

Alternatively, how should i check the field header names so that there is no confusion to Excel for proper run please?


PS - i just realized that one of the column need to convert from a text-string to numeric value, would that be a cause of the error? An idea how to use vba to convert "text to column" function please?

Thank you so much

Best Regards,
Joe