Hey All!
I'm stuck here. I have two VBA tasks I want to run. When I run them individually, they both work fine. When I try to combine them, the VBA runs in an infinite loop.
This code is looking for a change in a specific cell (which is a formula) and when it sees the change, it selects the cell, "presses" enter, and triggers a pivot table slicer update.
This code hides and blank rows or columns on the main tab of the workbook, where the data is displayed.
When I try to add the VBA code that hides the blank rows/columns to the code that performs the refresh, it ends up trying to refresh indefinitely. I'm assuming it has to something to do with the refresh being worksheet_calculate, but I don't know how to solve for it and google hasn't helped. (Perhaps I'm using the wrong search terms)
Any help would be greatly appreciated! Thank you!
I'm stuck here. I have two VBA tasks I want to run. When I run them individually, they both work fine. When I try to combine them, the VBA runs in an infinite loop.
This code is looking for a change in a specific cell (which is a formula) and when it sees the change, it selects the cell, "presses" enter, and triggers a pivot table slicer update.
VBA Code:
Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Range("J2")
If Not Intersect(Target, Range("J2")) Is Nothing Then
Sheets("Helper_Bulk").Select
Sheets("Helper_Bulk").Range("A1").Select
ActiveCell.FormulaR1C1 = "=Zone_MDM!R[3]C[8]"
Sheets("Helper_Bulk").Range("A2").Select
If Sheets("Helper_Bulk").Range("D1") = "Yes" Then
MsgBox "Note: There is no Bulk Data loaded for this zone. Please contact the MDM Team for assistance."
End If
Sheets("Helper_Rate").Select
Sheets("Helper_Rate").Range("A1").Select
ActiveCell.FormulaR1C1 = "=Zone_MDM!R[3]C[8]"
Sheets("Helper_Rate").Range("A2").Select
If Sheets("Helper_Rate").Range("D1") = "Yes" Then
MsgBox "Note: There is no Rate Data loaded for this zone. Please contact the MDM Team for assistance."
End If
Sheets("Helper_MAT").Select
Sheets("Helper_MAT").Range("A1").Select
ActiveCell.FormulaR1C1 = "=Zone_MDM!R[3]C[8]"
Sheets("Helper_MAT").Range("A2").Select
If Sheets("Helper_MAT").Range("D1") = "Yes" Then
MsgBox "Note: There is no Material Data loaded for this zone. Please contact the MDM Team for assistance."
End If
End If
Sheets("Main").Select
Range("A1").Select
End Sub
This code hides and blank rows or columns on the main tab of the workbook, where the data is displayed.
VBA Code:
Sub Hide()
StartRow = 12
EndRow = 200
ColNum = 12
For i = StartRow To EndRow
If Cells(i, ColNum).Value <> “TRUE” Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i
Dim c As Range
For Each c In Range("M9:AG9").Cells
If c.Value = "True" Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
When I try to add the VBA code that hides the blank rows/columns to the code that performs the refresh, it ends up trying to refresh indefinitely. I'm assuming it has to something to do with the refresh being worksheet_calculate, but I don't know how to solve for it and google hasn't helped. (Perhaps I'm using the wrong search terms)
Any help would be greatly appreciated! Thank you!