Paste Value Multiple Sheets
 

I have the following code to copy/paste value for each sheet in a
workbook.

Dim wks As Worksheet

For Each wks In Worksheets
wks.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next wks

The reason I am not naming each sheet is that these are first copied
from another workbook and the sheets that get copied over are not
always the same. This is the code right before the above, where myArr
is created from a loop.

Sheets(myArr).Copy

The code works through the first sheet, but then errors out.


--
LaraBee
------------------------------------------------------------------------
LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
View this thread: http://www.excelforum.com/showthread...hreadid=500917

Paste Value Multiple Sheets
 

You can only select cells on the active sheet (and that workbook has to be
active, too).

So add one extra line to select the sheet:

for each wks in worksheets
wks.select
wks.cells.select
....



Or you could drop the .select stuff.

Dim wks As Worksheet
For Each wks In Worksheets
with wks.Cells
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next wks



LaraBee wrote:

I have the following code to copy/paste value for each sheet in a
workbook.

Dim wks As Worksheet

For Each wks In Worksheets
wks.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next wks

The reason I am not naming each sheet is that these are first copied
from another workbook and the sheets that get copied over are not
always the same. This is the code right before the above, where myArr
is created from a loop.

Sheets(myArr).Copy

The code works through the first sheet, but then errors out.

--
LaraBee
------------------------------------------------------------------------
LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
View this thread: http://www.excelforum.com/showthread...hreadid=500917

--

Dave Peterson

Paste Value Multiple Sheets
 

Dave, you are my hero....you have saved me yet again. Thanks!!


--
LaraBee
------------------------------------------------------------------------
LaraBee's Profile: http://www.excelforum.com/member.php...o&userid=30275
View this thread: http://www.excelforum.com/showthread...hreadid=500917