On the last line of the following code fragment I get a runtime error
"Paste method of Worksheet class failed"

Worksheets(sheetToWorkOn).Rows(rowReport).Copy
While dataRow < 500
If Worksheets("Database").Cells(dataRow, partColumn).Value
= partToReportOn Then
valueToPlace = Worksheets("Database").Cells(dataRow,
1).Value
If Application.WorksheetFunction.isNumber(valueToPlac e)
And valueToPlace 0 Then
With Worksheets(sheetToWorkOn).Rows(rowReport)
..Interior.ColorIndex = 2
..Interior.Pattern = xlSolid
..Font.Size = 8
..RowHeight = 12
End With
Worksheets(sheetToWorkOn).Paste
Destination:=Worksheets(sheetToWorkOn).Rows(rowRep ort)


To make sure I do not accidentally try to paste into a range that's
different from the copy range I copied the entire row and paste the
entire row, but it still does not work.


--
pwermuth
------------------------------------------------------------------------
pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
View this thread: http://www.excelforum.com/showthread...hreadid=386292

Hi,

copy mode is sometimes released. it is also when a cell is changed.
if so, then 'paste' fails.
we should copy and paste at a time.

--
HTH

okaizawa

pwermuth wrote:
On the last line of the following code fragment I get a runtime error
"Paste method of Worksheet class failed"

Worksheets(sheetToWorkOn).Rows(rowReport).Copy
While dataRow < 500
If Worksheets("Database").Cells(dataRow, partColumn).Value
= partToReportOn Then
valueToPlace = Worksheets("Database").Cells(dataRow,
1).Value
If Application.WorksheetFunction.isNumber(valueToPlac e)
And valueToPlace 0 Then
With Worksheets(sheetToWorkOn).Rows(rowReport)
Interior.ColorIndex = 2
Interior.Pattern = xlSolid
Font.Size = 8
RowHeight = 12
End With
Worksheets(sheetToWorkOn).Paste
Destination:=Worksheets(sheetToWorkOn).Rows(rowRep ort)


To make sure I do not accidentally try to paste into a range that's
different from the copy range I copied the entire row and paste the
entire row, but it still does not work.

Thank you.

I figured it had something to do with the proximity of the copy an
paste statement.

However, I want to copy a line at the beginning of my Macro and the
paste it again and again using a loop. What better way to do that i
there than what I tried

--
pwermut
-----------------------------------------------------------------------
pwermuth's Profile: http://www.excelforum.com/member.php...fo&userid=2499
View this thread: http://www.excelforum.com/showthread.php?threadid=38629

Hi,
first of all, this 'Copy' method doesn't store cell value
to clipboard but only the address of range.
(actually text data is stored, however, it is not used
when we paste into cells.)
if you want to store cell value and restore it, you should copy it
onto another cell range or variables.

--
HTH
okaizawa

pwermuth wrote:
Thank you.

I figured it had something to do with the proximity of the copy and
paste statement.

However, I want to copy a line at the beginning of my Macro and then
paste it again and again using a loop. What better way to do that is
there than what I tried?

okaizawa wrote:
Hi,
first of all, this 'Copy' method doesn't store cell value
to clipboard but only the address of range.
(actually text data is stored, however, it is not used
when we paste into cells.)
if you want to store cell value and restore it, you should copy it
onto another cell range or variables.

Sorry, I withdraw my previous post.
I have not tested it enough.

the problem is that changing cell clears clipboard.
so, better to copy onto another cell range.

--
okaizawa