Daily Sales Report With Excel VBA

Personnel Based Daily Sales Report Template


          In this tutorial, in Data sheet we firstly created an Excel template by entering which staff sold how many products on which date .

At the top of Data sheet, we added two VBA button controls called Report and Delete Report Pages.

          ✔ When the user presses the Report button, new worksheets are created according to the names in column B, and the product sales made by each personnel by date are listed in these worksheets .

When the report button is pressed unnecessarily or repeatedly, repeated rows may occur on the created personnel sheets. If there are duplicate rows in the created pages belonging to the personnel, they are deleted with the following codes :
lastrow = Sheets(Page).Cells(Rows.Count, "B").End(xlUp).Row
    Set Rng = Sheets(Page).Range("A3:M" & lastrow)
    Rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13), Header:=xlYes

In this way, new records can be easily added without repeating the old records.

In order to avoid any problems in the macro, if there is the blank cell in column B of Data sheet, the row that contained blank cell is deleted as completely with that codes :
Dim hucre As Range, sonsatir As Long
Sheets("DATA").Range("B3").Select
sonsatir = Sheets("DATA").Cells(Rows.Count, "B").End(xlUp).Row
For Each hucre In Sheets("DATA").Range("B3:B" & WorksheetFunction.CountA(Range("B3:B" & sonsatir)))
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
Next


         ✔ After creating the sheets showing the sales amount of the personnel, a new menu item called Add-Ins is added to the Worksheet Menu Bar. A drop-down list is added to the new menu item by the macro. In this drop-down list all sheets of the workbook are listed. Thus, user can be easily navigated between the sheets of the workbook.


If desired, the created worksheets can be deleted by pressing "Delete Report Pages" button :
Sub Delete_Reports()
Application.DisplayAlerts = False
Again:
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "DATA" Then GoTo Skip
    Worksheets(i).Delete
    GoTo Again:
Skip:
Next i
Application.DisplayAlerts = True
Call ResetMenu
End Sub

The new menu item (drop down list on Add-Ins menu) is removed when the personnel sheets are removed or the workbook is closed. That is,Worksheet Menu Bar is reset .
Macro to remove Add-Ins menu :
Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
Err.Clear
End Sub

When the workbook is opened, the drop-down list that lists the sheets in the Add-Ins menu is added automatically.
Sub Auto_Open()
Call MakeCBO
End Sub
Really ,a useful Excel Macro .

No comments:

Post a Comment