Macros

[damanirahul]

I have 6 columns each for Date, Script, Quantity, Rate, Amount, Client.
Everyday i fill the data in this main sheet1.
I want in sheet2 the full details of date wise transaction done by client A should come. Please can you help me how to do it.

Thanks in advance.

 

[michaelnicolas]

Hello,

Is it possible to upload a sample workbook (sensitive data scrubbed) and show what you want to achieve? That would speed things up.

Kind regards

 

[damanirahul]

Thanks for your initiative. I registered yesterday, and this was my very first query. Can you please let me know how to upload my sample workbook so that you can see it.

 

[Sektor]

Forum doesn't let to upload files. You have to use some other storage place.

 

[damanirahul]

Thanks, but then how should i go so that my query is solved.

 

[damanirahul]

Date Script Name Quantity Rate Amount Client
1-Jul-11 ABC Ltd 100 105.00 10,500.00 A
1-Jul-11 CDE Ltd -50 97.00 -4,850.00 B
1-Jul-11 FGH Ltd 100 88.00 8,800.00 B
2-Jul-11 IJK Ltd -25 115.00 -2,875.00 C
2-Jul-11 ABC Ltd 500 115.00 57,500.00 A
2-Jul-11 CDE Ltd -200 79.00 -15,800.00 B
2-Jul-11 FGH Ltd 500 86.00 43,000.00 A
2-Jul-11 ABC Ltd 100 108.00 10,800.00 C
3-Jul-11 CDE Ltd -200 84.00 -16,800.00 C
3-Jul-11 IJK Ltd 100 117.00 11,700.00 C

This is sample of how my worksheet1 looks like, which I fill everyday.
I want in sheet 2 all the transactions done by A should come date wise automatically. Hope this will be helpful for board members to solve my query.

 

[michaelnicolas]

Hello,

1.Open your VB editor (from Excel Alt+F11).
2.Double-click Sheet2 (top left pane, under Microsoft Excel Objects)
3.Copy this code:

Private Sub Worksheet_Change(ByVal Target As Range)

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim DbLastRow As Long, RepLastRow As Long

If Target.Address = "$B$1" Then

    RepLastRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
        
        If RepLastRow <> 1 Then
            Sheet2.Range("A2:F" & RepLastRow).ClearContents
        End If

    DbLastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Sheet1.Range("A1:F" & DbLastRow).AutoFilter Field:=6, Criteria1:=Sheet2.Range("B1")
    Sheet1.Range("A1:F" & DbLastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A2")
    Sheet1.Range("A1:F" & DbLastRow).AutoFilter
    
End If

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

4. In Excel in Sheet2, type in cell A1: Enter Client
5. In cell B1 type "A" and enter


Hope that's what you want.

Kind regards

 

[damanirahul]

This works great, sir....
But I want in sheet2 only client A details to come, in sheet3 client B details should come and in sheet4 client C details should come.

Waiting for your feedback.

Kind Regards

 

[damanirahul]

Sir,

I'm waiting for your reply>>

Thanks n Kind Regards