Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Values in multiple sheets | Excel Discussion (Misc queries) | |||
search, copy and paste through multiple sheets | Excel Discussion (Misc queries) | |||
Search multiple sheets, then paste results in new sheet | Excel Programming | |||
Multiple Excel Sheets into one tab-delimited file - horizontal paste | Excel Programming | |||
Copy paste WkBk/sheet 1 to multiple wkbks/sheets | Excel Programming |