Macro Help Needed...copy into new sheet based on account number

[Have_Data_Will_Travel]

I have a weekly report that shows transactions for 7 accounts. I need to
break out the data into separate tabs based on the account number (all of
account #1's transactions in a tab, account #2 in a tab, etc). I have
written the macro to create the new sheets but I cannot figure out what the
VBA would look like for actually selecting the row(s) based on the account
number field and pasting it into a new sheet. This is going to be a variable
range report (different amount of transactions each week) so I need it to be
able to search the data, copy, and paste into the new sheets.

Any ideas???

 

[joel]

Sub MakeTabs()

Set OldSht = ActiveSheet
With OldSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data
.Rows("1:" & LastRow).Sort _
key1:=.Range("A1"), _
order1:=xlAscending, _
header:=xlYes

RowCount = 2
Start = RowCount
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) <> _
.Range("A" & (RowCount + 1)) Then

ID = .Range("A" & RowCount)
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = ID
'copy header row
.Rows(1).Copy Destination:=NewSht.Rows(1)
.Rows(Start & ":" & RowCount).Copy _
Destination:=NewSht.Rows(2)
Start = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Have_Data_Will_Travel]

WOW! Perfect...thank you! I'm just beginning to really learn VBA and this
was a huge help

"HDWT"

 

[joel]

You may want to change this line

from
Set OldSht = ActiveSheet
to
Set OldSht = Sheets("Sheet1")