Search code examples
vbaexcelinsertcode-formattinginventory-management

Add rows, duplicate workbook, import data and format

I am trying to create an Excel document for inventory management. I have already created a workbook where "daily sales" and "deliveries" are added and the sheet maintains an updated inventory, calculates income and net profit. This is all done in traditional Excel; However, this sheet has some problems, namely that I have to duplicate the sheet myself and change it for each month in the future (I am in a remote part of Africa where the people are not computer literate, and so the interface must be very simple).

I have recently discovered VBA Macros and have been writing them for this sheet. I have so far written a userform that has dropdown menus for month and year, and when you hit enter, the program duplicates a "master" document, autopopulates the dates across the top and saves the workbook as the input month and year. My questions are: How do I delete the last columns in the new workbook? In the Master sheet, there are 31 columns, but not all months have 31 days, so I want to delete the unnecessary columns, without deleting the "total" column that comes after. Once I have formatted the document, I would like to import the previous month's data from the last column of that inventory sheet.

This is the part of the code I am struggling with. I want to be able to delete the extra columns that are automatically filled in with the first couple of days of the next month, e.g., 28-Feb-16 then 1-Mar-16 then 2-Mar-16, where I can have the program find the March dates and delete their associated columns.

Private Sub CmdEnter_Click()
    'Duplicate Sheet
    Sheets(Array("Daily Sales", "Total Inventory", "Deliveries",_
      "Income Statement", "Profits")).Copy
    'Fill Dates in Daily Sales
    Sheets("Daily Sales").Activate
    'Enter combo boxes into first cell
    Range("B6").Select
    ActiveCell = CDate("1-" & CmboMonth.Value & "-" &_
      CmboYear.Value)
    'Fill in Month Dates
    Selection.AutoFill Destination:=Range("B6:AF6"), _
    Type:=xlFillValues
    'Auto-Size Columns
    Cells.Select
    Cells.EntireColumn.AutoFit
    '
     'Fill Dates in Total Inventory
    Sheets("Total Inventory").Activate
    'Enter combo boxes into first cell
    Range("C5").Select
    ActiveCell = CDate("1-" & CmboMonth.Value & "-" &_
      CmboYear.Value)
    'Fill in Month Dates
    Selection.AutoFill Destination:=Range("C5:AG5"),_
      Type:=xlFillValues
    'Auto-Size Columns
    Cells.Select
    Cells.EntireColumn.AutoFit
    '
     'Fill Dates in Deliveries
    Sheets("Deliveries").Activate
    'Enter combo boxes into first cell
    Range("B6").Select
    ActiveCell = CDate("1-" & CmboMonth.Value & "-" &_
      CmboYear.Value)
    'Fill in Month Dates
    Selection.AutoFill Destination:=Range("B6:AF6"),_
       Type:=xlFillValues
    'Auto-Size Columns
    Cells.Select
    Cells.EntireColumn.AutoFit
    '
     'Fill Dates in Income Statement
    Sheets("Income Statement").Activate
    'Enter combo boxes into first cell
    Range("C4").Select
    ActiveCell = CDate("1-" & CmboMonth.Value & "-" &_
      CmboYear.Value)
    'Fill in Month Dates
    Selection.AutoFill Destination:=Range("C4:AG4"),_
      Type:=xlFillValues
    'Auto-Size Columns
    Cells.Select
    Cells.EntireColumn.AutoFit
    '
     'Fill Dates in Profits
    Sheets("Profits").Activate
    'Enter combo boxes into first cell
    Range("E4").Select
    ActiveCell = CDate("1-" & CmboMonth.Value & "-" &_
      CmboYear.Value)
    'Fill in Month Dates
    Selection.AutoFill Destination:=Range("E4:AI4"),_
      Type:=xlFillValues
    'Auto-Size Columns
    Cells.Select
    Cells.EntireColumn.AutoFit
    'Save As
    ActiveWorkbook.SaveAs Filename:= _
      "Macintosh HD:Users:meringue90:Desktop:" & CmboMonth.Value &_
     CmboYear.Value , FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

I hope this makes sense. I should also point out that I am rather new to VBA.

Solution

  • The issue lies in this line and its equivalents:

    Selection.AutoFill Destination:=Range("B6:AF6"), Type:=xlFillValues

    You're filling from column B to column AF without any thought to how many days there are, and that's where the problem is. A more intelligent* way of filling in the dates will eliminate the need to delete columns to begin with.

    Try something like this variation on your original code:

    Private Sub CmdEnter_Click()
  Dim Days as Integer
  Dim StartDate as Date

    StartDate = CDate("1-" & CmboMonth.Value & "-" & CmboYear.Value)
    Days = DateDiff("d", StartDate, DateAdd("m", 1, StartDate)) - 1

    'Duplicate Sheet
    Sheets(Array("Daily Sales", "Total Inventory", "Deliveries",_
      "Income Statement", "Profits")).Copy
    '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

    'the rest of the code here...
    • Setting a variable called StartDate and calculating it once simplifies all the times you're calculating the date from your form. You can now replace every occurrence of CDate("1-" & CmboMonth.Value & "-" & CmboYear.Value) with StartDate. This makes your code easier to read, understand, and maintain.
    • Days is calculated using a couple of built in Excel functions:
      • DateAdd is handy for calculating a date in the future or past. I've used it to add 1 month to StartDate.
      • DateDiff is then used to calculate the difference between StartDate and one month from the StartDate. Then subtract 1 because you need to copy it one less time than the number of days in that month (i.e., you've already filled in the first day of the month).
    • Range("B6:AF6") hard coded your destination range, Range(Cells(), Cells()) is an alternate way of telling Excel what range you want by specifying the Row/Column (instead of Column/Row), and allowing you to use integers instead of letters for the columns. That makes programming much easier because you don't have to convert a calculated column number into a letter combination.
    • Simply duplicate the Selection.AutoFill... line for each of the sheets you need to put your dates on.

    *NOTE: "More intelligent" is in reference to the code, not the coder. We all had to start somewhere, and if you hadn't started and asked, you'd never learn.

    It's obvious that you've started with the Macro Recorder, and that's a great place to start. Unfortunately, if you rely on the Macro Recorder, you'll learn some bad habits in coding that will lead you to write many more lines that are much more difficult to follow than necessary. When you get this routine working, I'd recommend that you post your working code on Code Review and ask for some input there on making the code more readable and efficient. There are some great people there who will be happy to help you improve your code writing skills.