Filter rows/transactions to another sheet.

  • Thread starter Thread starter Tom Christensen
  • Start date Start date
T

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
 
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)
 
Hi Ron de Bruin

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

best regards
Tom
 
Back
Top