-1

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.

FreeMan
  • 5,660
  • 1
  • 27
  • 53
Meringue90
  • 5
  • 6
  • Your best bet would be to show the code you've got for duplicating your worksheet, and possibly a screen shot of what you've got. After that, someone will be willing to help you adjust your existing code. It's very unlikely that someone will be willing to just write it for you. – FreeMan May 10 '15 at 15:34
  • I apologize, you are absolutely correct, I have added what I have already written. – Meringue90 May 10 '15 at 19:26

2 Answers2

1

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.

Community
  • 1
  • 1
FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • Dear FreeMan, Thank you for your thorough response! I have taken your suggestions and they work perfectly! Regarding Macro Recorder, I have shamelessly relied on it to try to figure out what the heck I am trying to do. I will definitely post my end result here, and will subscribe to Code Review! Finally, do you know of any sites or books where I might learn more about VBA and coding? Is there some publication to tell one when to use Dim, or Long vs String, or this function vs that one? My vocabulary and grasp of these concepts is woefully poor. Thank you so much. – Meringue90 May 11 '15 at 14:15
  • Nothing wrong with using the Macro Recorder. I use it frequently when I need to adjust settings - it's much easier to record what Excel is doing than to try to look up some esoteric setting in the reference material. As for further help with coding - Google & attempts are probably your best bet. Just remember, if you're thinking "there's got to be a better way", there probably is. Search for what you're trying to do, and if you can't find it, post the code you've got and the problem you've run into here. Someone will pitch in to help. – FreeMan May 11 '15 at 14:35
  • @Meringue90 here's another question that will help move you in the right direction in your coding: [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – FreeMan May 11 '15 at 16:39
0

So, I have sort of solved my problem. The answer is cumbersome. I just set up a "Select Case" where I created a separate Case for each month that does not have 31 days, and had the program delete the right number of columns for each month. Here is what I have:

'Delete extra columns
            Select Case Me.CmboMonth.Value
            Case "February"
                Select Case Me.CmboYear.Value
                Case "2016"
                    Sheets("Daily Sales").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AH:AI").Select
                    Selection.Delete Shift:=xlToLeft
                Case "2020"
                    Sheets("Daily Sales").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AH:AI").Select
                    Selection.Delete Shift:=xlToLeft
                Case "2024"
                    Sheets("Daily Sales").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AH:AI").Select
                    Selection.Delete Shift:=xlToLeft
                Case "2028"
                    Sheets("Daily Sales").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AH:AI").Select
                    Selection.Delete Shift:=xlToLeft
                Case "2032"
                    Sheets("Daily Sales").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AE:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AF:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AH:AI").Select
                    Selection.Delete Shift:=xlToLeft
                Case Else
                    Sheets("Daily Sales").Select
                    Columns("AD:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AE:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AD:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AE:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AG:AI").Select
                    Selection.Delete Shift:=xlToLeft
                End Select
            Case "April" Or "June" Or "September" Or "November"
                    Sheets("Daily Sales").Select
                    Columns("AF:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Total Inventory").Select
                    Columns("AG:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Deliveries").Select
                    Columns("AF:AF").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Income Statement").Select
                    Columns("AG:AG").Select
                    Selection.Delete Shift:=xlToLeft
                    Sheets("Profits").Select
                    Columns("AI:AI").Select
                    Selection.Delete Shift:=xlToLeft
        End Select

Most definitely not elegant, but it does more or less what I need it to.

Meringue90
  • 5
  • 6