Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help with and ERROR Code

    I have the below code:

    Sub NewStatus()
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim TicketNumber As String, Status As String
    lastrow1 = Sheets("TicketStatus").Range("A" & Rows.Count).End(xlUp).Row

    For i = 3 To lastrow1
    TicketNumber = Sheets("TicketStatus").Cells(i, "A").Value
    Status = Sheets("TicketStatus").Cells(i, "H").Value

    Sheets("Baseline Information").Activate
    lastrow2 = Sheets("Baseline Information").Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow2
    If Status = "Status Changed" Then
    If Sheets("Baseline Information").Cells(j, "C").Value = TicketNumber Then
    Sheets("TicketStatus").Activate
    Sheets("TicketStatus").Range(Cells(i, "F"), Cells(i, "F")).Copy
    Sheets("Baseline Information").Activate
    Sheets("Baseline Information").Range(Cells(j, "D"), Cells(j, "D")).Select
    ActiveSheet.Paste
    End If
    End If

    Next j
    Application.CutCopyMode = False
    Next i
    Sheets("TicketInformation").Activate
    Sheets("TicketInformation").Range("A2").Select

    End Sub

    But I need it to paste the values instead of the Formula. When I change
    ActiveSheet.Paste
    to
    ActiveSheet.PasteSpecial xlPasteValues

    I get an error
    Run time error '1004' - PasteSpecial method of worksheet class failed.

    Please help.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Try the changed line in blue (not tested)

    Code:
    Sub NewStatus()
     Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
     Dim TicketNumber As String, Status As String
     lastrow1 = Sheets("TicketStatus").Range("A" & Rows.Count).End(xlUp).Row
    
     For i = 3 To lastrow1
     TicketNumber = Sheets("TicketStatus").Cells(i, "A").Value
     Status = Sheets("TicketStatus").Cells(i, "H").Value
    
     Sheets("Baseline Information").Activate
     lastrow2 = Sheets("Baseline Information").Range("A" & Rows.Count).End(xlUp).Row
    
     For j = 2 To lastrow2
     If Status = "Status Changed" Then
     If Sheets("Baseline Information").Cells(j, "C").Value = TicketNumber Then
     Sheets("TicketStatus").Activate
     Sheets("TicketStatus").Range(Cells(i, "F"), Cells(i, "F")).Copy
     Sheets("Baseline Information").Activate
     Sheets("Baseline Information").Range(Cells(j, "D"), Cells(j, "D")).Select
     Selection.PasteSpecial Paste:=xlPasteValues
     End If
     End If
    
     Next j
     Application.CutCopyMode = False
     Next i
     Sheets("TicketInformation").Activate
     Sheets("TicketInformation").Range("A2").Select
    
     End Sub

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That fixed it! Thank you!

Posting Permissions

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