Hi all,
I'm having trouble with part of my VBA code. I have a list of securities that I need to run a report for. The report uses the value in cell C3 to pull information from an external data source and then populates several pivot tables. I'm able to construct the for loop that cycles through the list, but it doesn't refresh the data/pivot tables before moving to the next security.
My code is below, I'd really appreciate anyone's help!
Sub Run_Holdings()
Do
Range("C3") = ActiveCell.Value
ThisWorkbook.RefreshAll
Run "RefreshAllPivotTables"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
End Sub
Sub RefreshAllPivotTables()
Dim PT As pivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable
Next PT
Next WS
End Sub
I'm having trouble with part of my VBA code. I have a list of securities that I need to run a report for. The report uses the value in cell C3 to pull information from an external data source and then populates several pivot tables. I'm able to construct the for loop that cycles through the list, but it doesn't refresh the data/pivot tables before moving to the next security.
My code is below, I'd really appreciate anyone's help!
Sub Run_Holdings()
Do
Range("C3") = ActiveCell.Value
ThisWorkbook.RefreshAll
Run "RefreshAllPivotTables"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
End Sub
Sub RefreshAllPivotTables()
Dim PT As pivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable
Next PT
Next WS
End Sub
