different sheets (ledgers)

[shaqil]

Deal All,

I have the following data in a file

JV # JV Dt Party Amount
1 21-07-07 abc 10
2 22-07-07 def 20
3 23-07-07 xyz 30
4 24-07-07 def 40
5 25-07-07 xyz 50

I need to create a macro that should prepare individual sheets for all
parties e.g "xyz" and has the following data

JV # JV Dt Party Amount Running Balance
3 23-07-07 xyz 30 30
5 25-07-07 xyz 50 80

Anybody can help me,

Thnx in advance.

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "C").Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = .Cells(i, "C").Value
Set sh = ActiveSheet
.Rows(1).Copy sh.Rows(1)
Else
If Application.CountIf(.Range("C1").Resize(i), .Cells(i,
"C").Value) = 1 Then
sh.Cells.ClearContents
.Rows(1).Copy sh.Rows(1)
End If
End If
.Cells(i, "A").Resize(, 4).Copy _
sh.Cells(sh.Rows.Count, "A").End(xlUp).Offset(1, 0)
Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)