converting file to "paste special------ values"

[zoharb]

Respected,
To convert excel file with functions in values only
normally with a single worksheet(page) file , its easy to copy everything and do "paste special -value"
but for a multi page file(I have a go to each page of 24 page file. and this is really time-consuming when done on daily basis)
This is done to keep the formatting
Can you please show me a way so that data in all worksheets are converted along with retaining formats
Zohar Batterywala

 

[Alex Blakenburg]

On a copy of your workbook see if this does what you need:

Sub ConvertAllSheetsToValues()

    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws.UsedRange
            .Value = .Value
        End With
    Next ws
End Sub