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???

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" wrote:

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???

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

"HDWT"


"joel" wrote:

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" wrote:

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???

You may want to change this line

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


"Have_Data_Will_Travel" wrote:

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

"HDWT"


"joel" wrote:

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" wrote:

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???