Hi to all
My Filed is like...
Date,Group,PartyName,Amount
1-july, Payment,abc,2345
1-july,Payment,xyz,6790
1-july,Recipt,pqr,8900
2-july,recipt,jjj,9000
2-july,payment,mmm,8907

I have data which i want to copy, Every day as per Date into new worksheet
with date name of worksheet. In New Worksheet it should be copy as per Group
filed, in group filed i have Payment & Recipt only.

It should copy like

Date:- 1-July
a1, b1, c1, d1, e1, f1
Payment, PartyName,Amount Recipt,Partyname,Amoumt.
Payment,abc,2345 Recipt,pqr,8900
Payment,xyz,6790

and if any new enrty done on old date it should copy into that date sheet
only.
Any help
Thanks

Tiya

put this in a standard module

Option Explicit

Sub PopulateData()
' assume input file has a worksheet called Data
Dim ws As Worksheet
Set ws = Worksheets("data")
Dim rw As Long ' index for reading data
Dim targetrow As Long ' row for writing data
Dim wsTarget As Worksheet ' where data is to go
Dim Col As Long ' used for setting pmt or rct column

rw = 2 'assumes first row is heading

Do Until ws.Cells(rw, 1).Value = ""
If InStr(UCase(Cells(rw, 2).Value), "REC") 0 Then
Col = 4
Else
Col = 1
End If
Set wsTarget = safeSheet(Format$(ws.Cells(rw, 1).Value, "dd-mmmm"))
targetrow = wsTarget.Cells(56000, Col).End(xlUp).Row + 1

With wsTarget
With .Range(.Cells(targetrow, Col), .Cells(targetrow, Col + 2))
.Value = ws.Range(ws.Cells(rw, 2), ws.Cells(rw, 4)).Value
End With
End With
rw = rw + 1
Loop


End Sub
Private Function safeSheet(sSheetName As String) As Worksheet
On Error Resume Next
Set safeSheet = Worksheets(sSheetName)
If Err.Number < 0 Then
Err.Clear
Set safeSheet = ThisWorkbook.Worksheets.Add
safeSheet.Name = sSheetName
If Err.Number < 0 Then GoTo trap
End If
On Error GoTo 0
Exit Function
trap:
MsgBox Err.Description, , "Error Adding Worksheet:" & sSheetName
On Error GoTo 0
End Function


"Tiya" wrote:

Hi to all
My Filed is like...
Date,Group,PartyName,Amount
1-july, Payment,abc,2345
1-july,Payment,xyz,6790
1-july,Recipt,pqr,8900
2-july,recipt,jjj,9000
2-july,payment,mmm,8907

I have data which i want to copy, Every day as per Date into new worksheet
with date name of worksheet. In New Worksheet it should be copy as per Group
filed, in group filed i have Payment & Recipt only.

It should copy like

Date:- 1-July
a1, b1, c1, d1, e1, f1
Payment, PartyName,Amount Recipt,Partyname,Amoumt.
Payment,abc,2345 Recipt,pqr,8900
Payment,xyz,6790

and if any new enrty done on old date it should copy into that date sheet
only.
Any help
Thanks

Tiya

Brilliant solution.
Can we also be able to "split" the data into 2 within a date sheet regarding
it beeing a "receipt" or "payment"?
Ragards
J_J

"Patrick Molloy" wrote in message
...
put this in a standard module


Option Explicit

Sub PopulateData()
' assume input file has a worksheet called Data
Dim ws As Worksheet
Set ws = Worksheets("data")
Dim rw As Long ' index for reading data
Dim targetrow As Long ' row for writing data
Dim wsTarget As Worksheet ' where data is to go
Dim Col As Long ' used for setting pmt or rct column

rw = 2 'assumes first row is heading

Do Until ws.Cells(rw, 1).Value = ""
If InStr(UCase(Cells(rw, 2).Value), "REC") 0 Then
Col = 4
Else
Col = 1
End If
Set wsTarget = safeSheet(Format$(ws.Cells(rw, 1).Value, "dd-mmmm"))
targetrow = wsTarget.Cells(56000, Col).End(xlUp).Row + 1

With wsTarget
With .Range(.Cells(targetrow, Col), .Cells(targetrow, Col + 2))
.Value = ws.Range(ws.Cells(rw, 2), ws.Cells(rw, 4)).Value
End With
End With
rw = rw + 1
Loop


End Sub
Private Function safeSheet(sSheetName As String) As Worksheet
On Error Resume Next
Set safeSheet = Worksheets(sSheetName)
If Err.Number < 0 Then
Err.Clear
Set safeSheet = ThisWorkbook.Worksheets.Add
safeSheet.Name = sSheetName
If Err.Number < 0 Then GoTo trap
End If
On Error GoTo 0
Exit Function
trap:
MsgBox Err.Description, , "Error Adding Worksheet:" & sSheetName
On Error GoTo 0
End Function


"Tiya" wrote:

Hi to all
My Filed is like...
Date,Group,PartyName,Amount
1-july, Payment,abc,2345
1-july,Payment,xyz,6790
1-july,Recipt,pqr,8900
2-july,recipt,jjj,9000
2-july,payment,mmm,8907

I have data which i want to copy, Every day as per Date into new
worksheet
with date name of worksheet. In New Worksheet it should be copy as per
Group
filed, in group filed i have Payment & Recipt only.

It should copy like

Date:- 1-July
a1, b1, c1, d1, e1, f1
Payment, PartyName,Amount Recipt,Partyname,Amoumt.
Payment,abc,2345 Recipt,pqr,8900
Payment,xyz,6790

and if any new enrty done on old date it should copy into that date sheet
only.
Any help
Thanks

Tiya