Problems with Paste

[pwermuth]

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(valueToPlace) 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(rowReport)


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.

[okaizawa]

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(valueToPlace)
> 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(rowReport)
>
>
> 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]

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]

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]

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