Reporting Macro using 3 reference cells in a range

[MrOreo]

Hello, I am currently working on a macro that would work with a spreadsheet containing people's information. As an example which is compatible with the code below, let's assume that the worksheet the formula is looking at, in column A will have a person's Location. Column B will have information which is irrelevant to my report. Columns C and D have other details which i need to report on.

So in a nutshell, I need the macro to look at column C, and if that matches the specified criteria, copy that row's cells A,C,D;

So.:

A      B      C                 D
UK    X      Accepted      100
UK    X      Accepted      100
UK    X      Rejected       150

*run macro*

Resulting sheet displays:

A      C                 D
 UK    Accepted      100
 UK    Accepted      100

Currently I am using this code. Which worked fine when the third cell in the range (Range("A" & x, "C" & x, "D" & x).Copy) was not in it. But as soon as I add a third cell to the range, the macro stops working.

Here is the full macro:

Public Sub CopyStages()
    Sheets(Range("lookup!C7").Value).Select
    ' Find the last row of data
    FinalRow = Range("A65536").End(xlUp).Row
    ' Loop through each row
    For x = 1 To FinalRow
        ' Decide if to copy based on column H
        ThisValue = Range("C" & x).Value
        If ThisValue = Range("lookup!C4").Value Then
            Range([COLOR=Red]"A" & x[/COLOR], "C" & x, "D" & x).Copy
            Sheets("destination").Select
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Sheets(Range("lookup!C7").Value).Select
        Else
        End If
    Next x
End Sub

Would anyone be kind enough to help out and tell me what I am doing wrong here?

Many thanks!

P.S. Ignore the comments within the macro, those are remnants from the early stages of it's development.

 

[JoeMo]

Maybe break it down like this:

Dim my3Cells as Range, rA As Range, rC As Range, rD As Range

Set rA = Range("A" & x)
Set rC = Range("C" & x)
Set rD = Range("D" & x)
Set my3Cells = Union(rA, rC, rD)
'Rest of code .... my3Cells.Copy etc