Filter rows/transactions to another sheet.

[Tom Christensen]

Hi

I have a sheet with 6500 transactions in rows. Each row contains an account
no, amount, etc

I would like to have Excel automatically copy all the rows/transactions on
each account to a separate sheet in the same workbook (one account per
sheet). And I would like this to be done automatically for each account in
the original sheet. I have tried Vlookup, but it does not seem suitable.

Can anyone give me inspiration?

Best Regards

Tom Christensen
Copenhagen/Denmark

 

[Ron de Bruin]

Hi Tom

Start here
http://www.rondebruin.nl/copy5.htm

 

[Chip Pearson]

Try some code like the following. Change

Set SourceWS = Worksheets("Sheet1") '<<<< CHANGE
to the worksheet containing the original data.

Change
Set SourceR = SourceWS.Range("A1") '<<< CHANGE
to the cell on which the original data starts


Sub AAA()
Dim SourceR As Range
Dim DestR As Range
Dim DestWS As Worksheet
Dim SourceWS As Worksheet

Set SourceWS = Worksheets("Sheet1") '<<<< CHANGE
Set SourceR = SourceWS.Range("A1") '<<< CHANGE

Do Until SourceR.Value = vbNullString
Set DestWS = Worksheets(SourceR.Text)
With DestWS
Set DestR = .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
End With
SourceR.EntireRow.Copy Destination:=DestR
Set SourceR = SourceR(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tom Christensen]

Hi Ron de Bruin

Thank you very much for your quick reply. And for the link.
)
I will search your "goldmine" of information for the best solution.

best regards
Tom