Excel Macro/VBA Help finding variable values from a variable range

[reilly]

Hi everyone, I have a nice problem for you to ponder on this nice Friday night.

I want/need a macro that will do the following.

On "Sheet1" I have a series of transaction number, that are not sequential, but always 8 digits long. The amount of them changes each month but they are located in column "A" on "Sheet1". So the values are variable and the number (range) of them is variable.

On "Sheet2" I have list of transaction numbers with extra data ranging from Column "A" to "F" including amount, text and other information. On this sheet there maybe more than one line that relates to the specified transaction number on "Sheet1". I need the macro to find these transaction lines from a mix of transaction numbers that are wanted and not wanted and copy the information to a new tab.



In short I want a macro that will
1. Look at a variable range and variable values on "Sheet1" in column "A" and "Find" these values on "Sheet2".
2. Copy the whole row of information relating to the transaction number and data from "Sheet2" to a "new sheet" taking into account there maybe numberous transactions lines with the same transaction number.


Any help on this matter will be appreciated, If it is even possible.

Thanks in advance people

 

[reilly]

Does matter guys, sorted it with this:

Sub CopyRows()

For i = 2 To Sheets("Transactions").Cells(Rows.Count, "A").End(xlUp).Row

Set vFound = Sheets("Data Set").Columns("A:A").Cells.Find(What:=Sheets("Transactions").Range("A" & i).Value, _
MatchCase:=False)

If Not vFound Is Nothing Then
vStart = vFound.Address
vMatch = True

Do
Sheets("Data Set").Range("A" & vFound.Row & ":" & "M" & vFound.Row).Copy Destination:= _
Sheets("Output Sheet").Range("A" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1 _
& ":" & "M" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1)

Set vFound = Sheets("Data Set").Columns("A:A").Cells.FindNext(vFound)
Loop Until vFound.Address = vStart
End If

Next i

If vMatch <> True Then
MsgBox ("No Matches Found")
End If


End Sub