Personnel Based Daily Sales Report Template
In this tutorial, we firstly created an Excel spreadsheet by entering to the Data sheet 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 Sheets.
✔ When the user pressed the Report button, new worksheets are created according to the names in column B, and the products that sold by each personnel based on the dates are listed in these worksheets.
✔ When the Report button is pressed unnecessarily or repeatedly, the repeated rows may occur in the created personnel sheets. If there are duplicate rows in the created sheets belonging to the personnel, they are deleted with the following codes . In this way, new records can be easily added without repeating the old records : ⬇
Dim Page As String
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 order to avoid any problems when creating new sheets according to the names in column B ; if there is the blank cell in column B , 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. Later , 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 Sheets” button. VBA codes that triggered by this 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
Sub MakeCBO()
Dim cboSheetz As CommandBarComboBox, ws As Worksheet
With Application
.ScreenUpdating = False
Run "ResetMenu"
With .CommandBars("Worksheet Menu Bar")
Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, before:=.Controls.Count)
End With
With cboSheetz
.Caption = "Sheet selector"
.OnAction = "mySheet"
.Width = 100
End With
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
cboSheetz.AddItem ws.Name
Next ws
cboSheetz.ListIndex = 1
.ScreenUpdating = True
End With
End Sub
Post a Comment