VBA Copy Paste Suddenly Stopped Working

[Zerb]

I have some VBA code that at the press of a button it executes a Sub consisting of a bunch of If then statements that look like this...

Dim StatusCol As Range

If AAAAAA = True Then
Set StatusCol = Worksheets("One").Range("D3:D500").Find("XXXXXX", , xlValues, xlWhole, , , False)
If Not StatusCol Is Nothing Then
    Worksheets("One").Range(StatusCol.Address).Offset(, -2).Resize(3, 6).Copy
    Worksheets("Two").Range("D500").End(xlUp).Offset(1, -2).PasteSpecial
End If
End If

This was working beautifully, until it wasn't. I use StatusCol as the range for each If Then statement and it seemed to be working as I said perfectly. Then all of a sudden it now won't paste onto Worksheet Two until I press the ENTER key and even once that is done it will only paste a certain portion of the If then statements where-as about 20 minutes ago it was posting everything properly. The first times it didn't work properly I would see the pasting happen on the sheet, then it would just disappear and leave me with either nothing or one line copied and pasted properly. What am I missing here?

 

[Marc L]

Another way :​

        Dim V
    With Worksheets("One")
        V = Application.Match("XXXXXX", .[D3:D500], 0)
        If IsNumeric(V) Then .Cells(V + 2, 2).Resize(3, 6).Copy [Two!D500].End(xlUp)(2, -1)
    End With

 

[Zerb]

Another way :​

        Dim V
    With Worksheets("One")
        V = Application.Match("XXXXXX", .[D3:D500], 0)
        If IsNumeric(V) Then .Cells(V + 2, 2).Resize(3, 6).Copy Range("Two!D500").End(xlUp)(2, -1)
    End With

I have setup the code i originally posted to only run certain sections and it still giving me the same issues. Thinking maybe I can isolate and find the spot where it trips up, to no avail. I have tried...

Application.ScreenUpdating = False at the beginning of my code, then turning it True at the end. I have also tried Application.CutCopyMode = False at the end of my code and that doesn't work either. It gets rid of the prompt to PRESS ENTER TO PASTE, but it doesn't automatically paste what it was pasting just an hour ago. I'm at a loss for how this was running just fine and has seemingly just decided to stop working here.

 

[Marc L]

What is the value of the variable V ?​

 

[Zerb]

I haven't tried your style code as of yet. I will continue to troubleshoot mine, which was working just fine this morning, and if I hit a brick wall I will change all of my If Then statements to what you posted and report back.

 

[Zerb]

I was able to fix my code, I ended up having a value down in the D column below my tables I had in the sheet that threw everything off.

I also tried your code and that did not accomplish what my code did once it work. Thanks though.