Need to reference macro data from a different sheet???

[Mark McInerney]

Hi All,

I have a short macro - see below. It scans through a range of text looking for a specific string, and if found, it populates range the hits onto another column. All works well, but I need to move the data from the current worksheet to another worksheet.

The new worksheet is called "DATA".

How and where do I change the reference from a1:a1000 to the new worksheet? I have tried DATA!a1:a1000 but it is not working. Any Help appreciated - Many Thanks - Mark.

Sub FindMatches()
Range("D:D").ClearContents
Drow = 1
For Arow = 1 To 1000
If InStr(1, Cells(Arow, 1), Cells(1, 2), vbTextCompare) > 0 Then
Cells(Drow, 4).Value = Cells(Arow, 1).Value
Drow = Drow + 1
End If
Next Arow
End Sub