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

On 6 Mrz., 14:08, Udo <udo.baumf...@arcor.de> 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....

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

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 <udo.baumf...@arcor.de> 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....
>
>

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 <udo.baumf...@arcor.de> 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....
> >
> >