Paste as Values

[bromy2004]

Hi Everyone,

I have a macro that Pastes everything as values, Deletes Buttons and deletes Hidden Data.

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

    For Each wsht In Worksheets
           With wsht
                If wsht.Visible = xlHidden Or wsht.Visible = xlVeryHidden Then
                    'unhide then delete sheets
                    Application.DisplayAlerts = False
                        wsht.Visible = xlSheetVisible
                        wsht.Delete
                    Application.DisplayAlerts = True
                Else
                'activate sheet if visible
                wsht.Activate
                'paste as values
                Cells.Select
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
                'delete hidden columns
                For lp = 256 To 1 Step -1 'loop through all columns
                    If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete
                Next
                'delete hidden Rows
                For lp = 65536 To 1 Step -1 'loop through all rows
                    If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete
                Next
            End If
        End With
    Next wsht
    'Delete Button

    For Each Buttons In ActiveSheet.Shapes
        If Buttons.Type = 8 Then
        Buttons.Delete
    End If
Next

Application.Calculation = Calc
Application.ScreenUpdating = True

ActiveSheet.Range("A1").Select
End Sub

however i want to change it to only "Paste As Values" if it refers to something Hidden.

but i dont know how to do that.

Can anyone help?

-Bromy