VBA to update Pivots

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Team,

I need you help again. I have to run, on daily base, 5 massive reports, each is stored in a seperate tab of one workbook.

In the tabs a macro is doing its job performing xlookups, calculations, if requests etc.
One of those macro task is to create a helper column, doing a xlookup, deleting all rows which contains a certain value found in the xlookup in the helper column. Then the helper column gets deleted and the macro moves on to other tasks.
In a White Board tab I have now some pivots referring to the tabs, the macro was working on.
Everytime I have to change the data source to e.g. $A:$L. Because after the macro run the Data source range is only $A:$K. And if I would not change the data sorurce range, the next day the data source range would be reduced by one column more to e.g. $A:$J.

I tried this:

'Dim Data_Sheet As Worksheet
'Dim Pivot_Sheet As Worksheet
'Dim StartPoint As Range
'Dim DataRange As Range
'Dim PivotName As String
'Dim NewRange As String
'Dim LastCol As Long
'Dim lastRow As Long
'
''Set Pivot Table & Source Worksheet
'Set Data_Sheet = ThisWorkbook.Worksheets("FBL5n_incoming_cash")
'Set Pivot_Sheet = ThisWorkbook.Worksheets("White_Board")
'
''Enter in Pivot Table Name
'PivotName = "Incoming cash calendar week"
''Defining Staring Point & Dynamic Range
'Data_Sheet.Activate
'Set StartPoint = Data_Sheet.Range("A1")
'LastCol = StartPoint.End(xlToRight).Column
'DownCell = StartPoint.End(xlDown).Row
'Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
'NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
'
''Change Pivot Table Data Source Range Address
'Pivot_Sheet.PivotTables(PivotName). _
'ChangePivotCache ActiveWorkbook. _
'PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
'
''Ensure Pivot Table is Refreshed
'Pivot_Sheet.PivotTables(PivotName).RefreshTable
'

But it is not working. I'm not sure if the code is incorrect.

Sorry for the novel.

Thank you all in advance.

Cheers

Chris
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top