Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accessing ranges with integer variables | Excel Programming | |||
Named Ranges - accessing a cell | Excel Discussion (Misc queries) | |||
Accessing named ranges in Excel from VBA | Excel Discussion (Misc queries) | |||
Accessing hidden sheets | Excel Programming | |||
Accessing Named Ranges | Excel Programming |