Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: paste method of worksheet class failed

    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

  2. #2
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Paste method of worksheet error

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •