Hidden Columns With Cell References

i have a Macro that gets rid of Hidden columns and Rows.
but i kept getting REF# errors.
so i changed the macro to Paste as values before the rest of the macro.

but then my Subtotal's dont work.

macro that only Paste the values of formulas that refer to a hidden cell?


Sub Prepare_Workbook()

Dim lp As Double
Dim wsht As Worksheet
Dim Buttons As Object
Dim Calc As String

Calc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False