Solved: Moving rows from one sheet to another using vlookup

[Klartigue]

Attached is a spreadsheet. The Volume Worksheet sheet (sheet 2) has all the data that will be copied and pasted into the Broker Volume Master spreadsheet table (sheet 1). For example, in the volume worksheet, is there a way to move Row 2 which has broker Angel Oak Capital into the Broker Volume Master Spreadsheet. Basically a way to use a macro that looks up the broker on the volume worksheet, goes into the the drop down in Column A1 of the broker volume master sheet, selects only the correct broker, and inserts that row into the next available row of the table?

Thanks for the help, you all have really be very helpful!

[Klartigue]

actually i dont need to get that complicated. All i need to do is select all the rows in the volume sheet and have them copy and pasted into the table on the broker volume sheet

[mancubus]

hi.

Broker Volume Master Worksheet - Column A: unique broker names.

In Volume Worksheet - Column F : repeated broker names (brokers that made multiple transactions, i believe.)

Broker Volume Master Worksheet - Row 1: 8 column headings, 4 of which are from 24 headings (row1) in Volume Worksheet.

if you want to copy-paste all data from Volume to Broker Volume Master, no problem (1); if you wish only related columns be copied, no problem (2).

but if you want stg else, you should be more specific.

(1)
[vba]
Sub CopyAllData()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long, LastCol As Long

Set ws1 = Sheets("Volume")
Set ws2 = Sheets("Broker Volume Master")

LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

ws1.Activate
Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
ws2.Range("A1").PasteSpecial xlValues
Application.CutCopyMode = False

End Sub
[/vba]


(2)
[vba]
Sub CopyRelatedCols()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Range, c As Range, rng As Range
Dim LastRow As Long
Dim s As String

Set ws1 = Sheets("Volume")
Set ws2 = Sheets("Broker Volume Master")

For Each c In ws2.Range("A1:H1")
s = c.Value
Set r = ws1.Cells.Find(what:=s, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
If Not r Is Nothing Then
ws1.Activate
LastRow = Cells(Rows.Count, r.Column).End(xlUp).Row
Range(Cells(r.Row, r.Column).Offset(1, 0), Cells(LastRow, r.Column)).Copy Destination:=ws2.Cells(c.Row, c.Column).Offset(1, 0)
End If
Next

End Sub[/vba]

[Klartigue]

Thanks!!