Accessing ranges within sheets
 

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

Accessing ranges within sheets
 

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

Accessing ranges within sheets
 

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

Accessing ranges within sheets
 

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

Accessing ranges within sheets
 

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

Jim Thomlinson


"Joel" wrote:

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