Monthly sales summary.

[TUNGANA KURMA RAJU]

what code will give the following results.
Col A to Col c ,the database is updated daily thus the sales figures in Col c
are added every day, date wise( col b) and sales man wise(Col a).I want
a monthly summary report salesman wise sales done by salesman in a
month.If i put month Jan-2009 in E1, salesmanwise sales summary for the
month of Jan-09 be displayed from E2:Fn.

 

[Per Jessen]

Hi

See if this is what you want:

Sub Report()
Dim TargetMonth As Integer
Dim SalesMan() As String
Dim FilterRange As Range
Dim TargetRange As Range
Dim c As Long
Dim off As Long

Application.ScreenUpdating = False
Range("E2", Range("F2").End(xlDown)).ClearContents
TargetMonth = Month(Range("E1").Value)
Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible)
ReDim SalesMan(TargetRange.Cells.Count - 1)
For Each cell In TargetRange
SalesMan(c) = cell.Value
c = c + 1
Next
ActiveSheet.ShowAllData

For c = 1 To UBound(SalesMan)
Range("E2").Offset(off, 0).Value = SalesMan(c)
FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c)
Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible)
FilterRange.AutoFilter
For Each r In TargetRange.Rows
If r.Row > 1 Then
If Month(Cells(r.Row, 2).Value) = TargetMonth Then
Range("F2").Offset(off, 0) = Cells(r.Row, 3).Value
off = off + 1
End If
End If
Next
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per

 

[TUNGANA KURMA RAJU]

Many thanks,Per,
you are almost very close to the results I need.
The output is coming like this;
from E2
Raj 100
200
300
75
john 200
290
mary 100
100
700
The output I need is sum of these values salesman wise.
Like this
from E2
Raj 675
john 490
mary 900

 

[Per Jessen]

Hi TK Raju

This should do it:

Sub Report()
Dim TargetMonth As Integer
Dim SalesMan() As String
Dim FilterRange As Range
Dim TargetRange As Range
Dim c As Long
Dim off As Long
Dim SumValue As Double

Application.ScreenUpdating = False
Range("E2", Range("F2").End(xlDown)).ClearContents
TargetMonth = Month(Range("E1").Value)
Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible)
ReDim SalesMan(TargetRange.Cells.Count - 1)
For Each cell In TargetRange
SalesMan(c) = cell.Value
c = c + 1
Next
ActiveSheet.ShowAllData

For c = 1 To UBound(SalesMan)
Range("E2").Offset(off, 0).Value = SalesMan(c)
FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c)
Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible)
FilterRange.AutoFilter
For Each r In TargetRange.Rows
If r.Row > 1 Then
If Month(Cells(r.Row, 2).Value) = TargetMonth Then
SumValue = SumValue + Cells(r.Row, 3).Value
End If
End If
Next
Range("F2").Offset(off, 0) = SumValue
off = off + 1
SumValue = 0
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per

 

[TUNGANA KURMA RAJU]

A million thanks Per Jessen.
I am so much thankful,it gave me much relief.
Thanks once again for your help.