For Each sht In ThisWorkbook.Sheets

sht.Cells.Copy
sht.Cells.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next sht

"Udo" wrote:

On 6 Mrz., 14:08, Udo wrote:
Hi Ex(cel)perts,

I thought I'd have a quick solution, but Excel doesn't agree. I have
multiple sheets in one workbook and from a specific one on I would
like to copy the content of that sheet into itself, just to overwrite
the formulas.
The start of the makro is this:
Private sub Finalise_click()
Dim Number as Integer ' to store the number of worksheets
Dim k as integer ' loop variable
for k=3 to Number

I entcoutered two problems:
When I countinue with
sheets(k).select
then I get an error, it will not recognise the sheet. If I use the
real name of the sheet like Sheets("starter").select, it works.

Second problem:
If I try another way, e.g. say
ActiveSheets.next.select
Range("A1:AB200").Select
Excel jumps to the next sheet, but it can't select that range. What is
my mistake?
How can loop through all the sheets and substitute the formulas with
the values?

Thank you in advance.
Udo

PS: sorry for the confusion and sending too early....