hi
winxp/2k
excel 2k

I wrote a couple of routines to one, the check values and colors, and two copy from sheet2 and paste on sheet1. Everything works well with the exception of this part of my code, which is part of the commandbutton_click event:

.
.

If mycounter = 5 Then
Range("A" & j).Value = "**"
Range("A" & j).Interior.Color = vbGreen
mycounter = 0
Range("A" & j).Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Exit Sub

.
.

i get this error:

Run-Time error '1004'
Paste method of worksheet class failed.

when I click on debug, this line is highlited:

"ActiveSheet.Paste"


here is a sample of values in sheet1:
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
**
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23
**
RICHARD 8/2/2004 17 0 1 0 0:15:41 4:41:37 1:18:23

Here is the code:

this method checks already existing values in cheet1 and either paints the value yellow or red or adds "**" after the mycounter = 5

Sub check()
Sheet1.Activate
'Dim i, j, red, mycounter As Integer
Dim i, j, red As Integer
j = ActiveSheet.UsedRange.Rows.Count
red = 0
mycounter = 0
For i = 1 To j
Cells(i, "a").Select

If Cells(i, "a").Value = "RICHARD" Then
If mycounter = 5 Then
Cells(i, "A").Value = "**"
Cells(i, "a").Interior.Color = vbGreen
mycounter = 0
Else
mycounter = mycounter + 1
Cells(i, "a").Interior.Color = vbYellow
End If
ElseIf Cells(i, "a").Value = "**" Then
Cells(i, "a").Interior.Color = vbGreen
mycounter = 0

ElseIf Cells(i, "a").Value = "" And Cells(i, "a").Value <> "**" And mycounter < 5 And mycounter <> 0 Then
Cells(i, "a").Interior.Color = vbRed
red = red + 1
mycounter = mycounter + 1
ElseIf (mycounter = 5 And Cells(i, "a").Value = "") Or (mycounter = 5 And Cells(i, "a").Value = "RICHARD") Then
Cells(i, "A").Value = "**"
Cells(i, "a").Interior.Color = vbGreen
mycounter = 0

ElseIf mycounter < 5 And Cells(i, "a").Value = "" And Cells(i, "a").Value <> "**" Then
mycounter = mycounter + 1
Cells(i, "a").Interior.Color = vbRed
red = red + 1
End If


Next i
End Sub


this method copies and paste the values:

Private Sub CommandButton3_Click()
check
Sheet2.Activate
Dim i As Integer

For i = 1 To 7
'Sheets("Sheet2").Select
Sheet2.Range("A" & i).Select

If Sheet2.Range("A" & i).Value = "RICHARD" Then
Sheet2.Range("A" & i & ":J" & i).Select
Selection.Copy
'Sheets("Sheet1").Select
Sheet1.Activate
counter = ActiveSheet.UsedRange.Rows.Count + 1
For j = 1 To counter
Range("A" & j).Select
If Range("A" & j).Value = "RICHARD" Or Range("A" & j).Interior.Color = vbRed Then
'j = j + 1
ElseIf Range("A" & j).Value = "**" Then
'j = j + 1
ElseIf Range("A" & j).Value = "" Or Range("A" & j).Interior.Color <> vbRed Or Range("A" & j).Value <> "**" And mycounter = 0 Then
If mycounter = 5 Then
Range("A" & j).Value = "**"
Range("A" & j).Interior.Color = vbGreen
mycounter = 0
Range("A" & j).Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Exit Sub
End If
If mycounter < 5 Or mycounter = 0 Then

If mycounter < 5 Then
Range("A" & j).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Exit Sub
End If
If mycounter = 0 Then
Range("A" & j).Value = "**"
Range("A" & j).Interior.Color = vbGreen
mycounter = 0
End If
End If
End If

Next j
End If

Next i
'Sheet1.Range("A1").Select
End Sub

can anybody help?

thanks

Disregard my post. I figured out the problem.

Just in case you wanted to know, the action of adding the color green emptied the values in the buffer. That line:

Range("A" & j).Interior.Color = vbGreen

caused the problem.

I remedied it by offsetting the cell up after pasting and painted it green.

Done.