Code Help!!

[asdaftasabrush]

Anyone know why this line of code is failing?


Private Sub GenerateRedsNights_Click()
Sheets("Red Alerts").Range("A5:M500").ClearContents
If Sheets("General Areas Sauce").Cells("D20:D59").Value = "R" Then
Sheets("General Areas Sauce").Cells("G20:G59").cellcopy Destination:=Sheets("Red Alerts").Range("F5" & Rows.Count).Offset(1)
End If
End Sub

 

[My Aswer Is This]

Try this:

This scripts assumes your sheets are named "Sheet 1" and "Sheet 1" with the space like you showed in your post. And it will search all the way down Column "B" looking for "R" and not just B2 to B4 like you said in your post I think this was a mistake on your part.

In one place you said:
then placed onto "Sheet 2 and Sheet 1"
and another place you said sheet Report I used sheet 1 and sheet 2

Sub Test()
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrowa = Sheets("Sheet 2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrow = Sheets("Sheet 1").Cells(Rows.Count, "B").End(xlUp).Row + 1
Dim c As Range
Sheets("Sheet 1").Activate
    For Each c In Sheets("Sheet 1").Range("B2:B" & Lastrow)
        If c.Value = "R" Then
            c.Offset(0, 3).Copy Destination:=Sheets("Sheet 2").Range("A" & Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub