nat,

I am at work now. If no one else answers this I'll try to figure out later.

Doug

"nat" wrote in message
...
Thank you Doug.
The continuation character fixed the syntax error;
however, when I've tried to run it on my test table, the
total on sheet2 for the las account "3333" is correct
(=210) but only one row (with $200 balance) shows up. Any
idea why the row with $10 balance is not showing? Can you
correct the code? Thank you in advance.
-----Original Message-----
I haven't been following along here, but it looks like
the "line
continuation" character(underscore followed by a space)
in the middle of the
Copy statement is missing, i.e.:

Rows(iStart & ":" & iEnd - 1).Copy _
Destination:=Worksheets("Sheet2").Cells(iRow, "A")

Could just be the formatting on my screen, but it looks
suspicious.

hth,

Doug


"nat" wrote in
message
...
Bob,
Thank you very much for the code. When I've tried to
run
it, i've got a syntax error in Destination:= line. Any
idea why?
-----Original Message-----
Nat,

Sorry about that. I noticed the condition, but built
the
basic code first,
and then forgot it.

Try this instead

Sub Accounts()
Dim i As Long, iRow As Long
Dim iStart As Long, iEnd As Long
Dim cLastRow As Long
Dim nACcount As Long, nBalance As Long
Dim fFirst As Boolean
Dim sRows As String

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
nACcount = Range("B2").Value
nBalance = 0
iRow = 1
iStart = 2: iEnd = iStart
fFirst = True
For i = 2 To cLastRow
If Cells(i, "B").Value = nACcount Then
iEnd = iEnd + 1
nBalance = nBalance + Cells(i, "C").Value
Else
nACcount = Cells(i, "B").Value
If nBalance 50 Then
Rows(iStart & ":" & iEnd - 1).Copy
Destination:=Worksheets("Sheet2").Cells(iRow, "A")
iRow = iRow + iEnd - 1 - iStart + 1
With Worksheets("Sheet2").Cells
(iRow, "C")
.Value = nBalance
.Font.Bold = True
End With
iRow = iRow + 1
End If
nBalance = 0
iStart = iEnd
i = i - 1
End If
Next i

If nBalance 50 Then
Rows(iStart & ":" & iEnd - 1).Copy
Destination:=Worksheets("Sheet2").Cells(iRow, "A")
With Worksheets("Sheet2").Cells(iRow + 1, "C")
.Value = nBalance
.Font.Bold = True
End With
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"nat" wrote in
message
...
Bob
I'm not very familiar with the code but I think it
should
have one more condition:
- if the balance per account is equal or greater
than $50.00, than it should copy the rows to another
sheet
- if not, than no copy needed.

Can you modify to reflect this condition? Thank you.

-----Original Message-----
Nat,

Is this what you want?

Dim i As Long
Dim cLastRow As Long
Dim iRow As Long
Dim nACcount As Long
Dim nBalance As Long
Dim fFirst As Boolean

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
nACcount = Range("B2").Value
nBalance = 0
iRow = 1
fFirst = True
For i = 2 To cLastRow
If Cells(i, "B").Value < nACcount Then
nACcount = Cells(i, "B").Value
With Worksheets("Sheet2").Cells
(iRow, "C")
.Value = nBalance
.Font.Bold = True
End With
nBalance = 0
iRow = iRow + 1
End If
nBalance = nBalance + Cells(i, "C").Value
Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("Sheet2").Cells
(iRow, "A")
iRow = iRow + 1
Next i
nACcount = Cells(i, "B").Value
With Worksheets("Sheet2").Cells(iRow, "C")
.Value = nBalance
.Font.Bold = True
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"nat" wrote in
message
...
Hello,
Can someone help with the code, please?
Here is the example of the table (let's say on
Sheet1):
A B C
1 Depart Num Acct Num Acct Balance
2 01 1111 100.00
3 02 1111 50.00
4 03 2222 150.00
5 01 3333 200.00
6 05 3333 10.00

I need the macro to do the following:
- if the total balance per account is equal or
greater
than $50
- select all rows for that account, and
- copy them to Sheet2
- after all rows (meeting the requirements are
copied),
insert lines to subtotal balance for each account.
Note: the number of rows in the first table will
be
changing on a monthly basis.



.



.



.