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
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
