Accessing ranges within sheets

[Udo]

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()


I entcoutered two problems

 

[Udo]

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....

 

[merjet]

Try this. Change loop to suit.

Sub Macro1()
Dim iCt As Integer
For iCt = 3 To 4
Sheets(iCt).UsedRange.Copy
Sheets(iCt).UsedRange.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next iCt
End Sub

Hth,
Merjet

 

[Joel]

For Each sht In ThisWorkbook.Sheets

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

 

[Jim Thomlinson]

or even shorter...

sub WhatEver()
dim sht as worksheet

for each sht in thisworkbook.worksheets
sht.cells.value = sht.cells.value
next sht
End sub