From New Worksheet, AutoFill Dates, Delete Extra Columns, Save As Month and Year

I have created an inventory management sheet for a Lodge in Africa. The general conditions of the document are that it has to be easy to use, because the people who are using it are not very computer literate. I have generated a "generic" master document where I have pre-entered the items for sale in Column A, and then left 31 pre-formatted spaces for dates across the top. The cells inside the various sheets are pre-populated with formulas for calculating total inventory, income and net profits based on deliveries and daily sales.

I have created this macro that will take this generic sheet and, based on an input of month and year, will generate a new monthly sheet which will autopopulate the dates across the top of the sheet, and will delete the columns that are not necessary based on the number of days in the month. I got help on the CmdEnter_Click portion of this code from StackOverflow. This code works; however, it is extremely clumsy and long-winded. You will note that I have relied extensively on Macro Recorder, because I am VERY new to VBA. I thank you for any input you may have.

```
Option Explicit
Private Sub CmboMonth_DropButtonClick()
'Populate Control
Me.CmboMonth.AddItem "January"
Me.CmboMonth.AddItem "February"
Me.CmboMonth.AddItem "March"
Me.CmboMonth.AddItem "April"
Me.CmboMonth.AddItem "May"
Me.CmboMonth.AddItem "June"
Me.CmboMonth.AddItem "July"
Me.CmboMonth.AddItem "August"
Me.CmboMonth.AddItem "September"
Me.CmboMonth.AddItem "October"
Me.CmboMonth.AddItem "November"
Me.CmboMonth.AddItem "December"
End Sub

Private Sub CmboYear_DropButtonClick()
'Populate Control
Me.CmboYear.AddItem "2015"
Me.CmboYear.AddItem "2016"
Me.CmboYear.AddItem "2017"
Me.CmboYear.AddItem "2018"
Me.CmboYear.AddItem "2019"
Me.CmboYear.AddItem "2020"
Me.CmboYear.AddItem "2021"
Me.CmboYear.AddItem "2022"
Me.CmboYear.AddItem "2023"
Me.CmboYear.AddItem "2024"
Me.CmboYear.AddItem "2025"
Me.CmboYear.AddItem "2026"
Me.CmboYear.AddItem "2027"
Me.CmboYear.AddItem "2028"

The first thing to note is that you populate the combo boxes each time they're clicked. There's no need to do it each and every time. You can do it once on the UserForm_Intialize event.

Private Sub UserForm_Initialize()

    InitializeMonthsCombo
    InitalizeYearsCombo

End Sub

There's also a trick or two to slim down the amount of code you need to populate their values. For the "years" box, you're adding each year between a start year and an end year. We can populate this using a loop.

Private Sub InitalizeYearsCombo()

    Const startYear As Integer = 2015
    Const endYear As Integer = 2035

    Dim i As Integer
    For i = startYear To endYear
        Me.CmboYear.AddItem i
    Next

End Sub

We can use a similar method for the months, but first, we need to populate a string array so that we have something to loop over.

Private Sub InitializeMonthsCombo()

    Dim months() As String
    months = Split("January,Februrary,March,April,May,June,July,August,September,October,November,December", ",")

    Dim i As Integer
    For i = LBound(months) To UBound(months)
        Me.CmboMonth.AddItem months(i)
    Next

End Sub

The next thing to do is start Avoiding Activate and Select. We should always default to explicitly referencing objects instead. For example, this snippet

'Fill Dates in Daily Sales
    Sheets("Daily Sales").Activate
'Enter combo boxes into first cell
    Range("B6").Select
    ActiveCell = StartDate

could be re-written like so.

Sheets("Daily Sales").Range("B6").Value = StartDate

But we'll need to go a bit farther in order to really clean up your code. Let's declare a variable for the Daily Sales worksheet. This is because once we stop Selecting/Activating, we can no longer use the plain Cells object which implicitly refers to the active sheet.

This

'Fill Dates in Daily Sales
        Sheets("Daily Sales").Activate
    'Enter combo boxes into first cell
        Range("B6").Select
        ActiveCell = StartDate
    'Fill in Month Dates
        Selection.AutoFill Destination:=Range(Cells(6, 2), Cells(6, 2 + Days)), Type:=xlFillValues
    'Auto-Size Columns
        Cells.Select
        Cells.EntireColumn.AutoFit

Becomes this

Dim dailySales As Worksheet
    Set dailySales = Worksheets("Daily Sales")

    Dim rng As Range
    Set rng = dailySales.Range("B6")

    rng.Value = StartDate
    rng.AutoFill Destination:=Range(dailySales.Cells(6, 2), dailySales.Cells(6, 2 + Days)), Type:=xlFillValues

    dailySales.Cells.EntireColumn.AutoFit

Which allows us to extract all of the duplicated code out into a subroutine of it's own.

Public Sub Populate(destSheet As Worksheet, first As Range, dest As Range)

    first.Value = StartDate
    first.AutoFill Destination:=dest, Type:=xlFillValues

    destSheet.Cells.EntireColumn.AutoFit

End Sub

Which reduces each call to just a couple of lines of code.

Dim dailySales As Worksheet
Set dailySales = Worksheets("Daily Sales")

Populate dailySales, dailySales.Range("B6"), dailySales.Range(dailySales.Cells(6, 2), dailySales.Cells(6, 2 + Days))

Dim totalInventory As Worksheet
Set totalInventory = Worksheets("Total Inventory")

Populate totalInventory, totalInventory.Range("C6"), totalInventory.Range(totalInventory.Cells(5, 3), totalInventory.Cells(5, 3 + Days))

I would probably go one step farther and create a function to return this range based off of the hard coded numbers and number of days. This logic also gets repeated a lot.

totalInventory.Range(totalInventory.Cells(5, 3), totalInventory.Cells(5, 3 + Days))

I'll leave it as an exercise for you to apply these concepts to your Select Case block and encourage you to come back and post a follow up question once you've cleaned the code up a bit.

StackExchange Code Review Q#90510, answer score: 5