Loop through all Sheets, PasteValues

Hi

Please could someone help with the code for looping through ALL the sheets in a workbook, copying ALL the cells and pasting the values?

Many thanks

Roasty

Courtesy of Aaron Blood

Sub Formula_Zapper() 
    Worksheets.Select 
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues 
    ActiveSheet.Select 
    Application.CutCopyMode = False 
End Sub

Have you hidden sheets?

Originally Posted by royUK

Have you hidden sheets?

Unhide all the sheets, code now works

Thanks

Roasty

Try this

Option Explicit 
 
Sub Formula_Zapper_MkII() 
     
    Dim sh As Worksheet, HidShts As New Collection 
    For Each sh In ActiveWorkbook.Worksheets 
        If Not sh.Visible Then 
            HidShts.Add sh 
            sh.Visible = xlSheetVisible 
        End If 
    Next sh 
     
    Worksheets.Select 
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues 
    ActiveSheet.Select 
    Application.CutCopyMode = False 
     
    For Each sh In HidShts        
        sh.Visible = xlSheetHidden 
    Next sh 
     
End Sub