current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 1 down vote favorite

I am writing a macro for an inventory management document. The point of the User Form is to allow the user to add a new item to each of 5 sheets (Daily Sales, Total Inventory, Deliveries, Income Statement, Profits), directly into a dynamic named range. The user provides Item Name, category (Cool Drinks, Beer and Cider, Bitters, etc...), number of servings per bottle (20, 30, 200 or "else"), purchase price, and sales price. The sheet then inserts a new row on each page, and adds the information into the appropriate places. The formulas are different for 20 servings or 30 or 200, so I copy a generic formula from "C1", "C2", "C3" and "C4". I am new to VBA, so a lot of this is hard coded.

I know I need to create subroutines, but I do not really understand how to do that when certain information changes with each selection.

Private Sub CmdEnter_Click()
Dim InsertRange As Range
Dim ItemEntryRange As Range
Dim iColumns As Integer

Select Case Me.CmboItemType.Value
Case "Cool Drinks"
    'Cool Drinks
        'Daily Sales CoolDrinks
            Set InsertRange = Worksheets("Daily   
Sales").Range("CoolDrinksSales")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1),  
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Daily 
Sales").Range("CoolDrinksSales")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Deliveries CoolDrinks
            Set InsertRange = 
Worksheets("Deliveries").Range("CoolDrinksDeliveries")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Deliveries").Range("CoolDrinksDeliveries")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Total Inventory CoolDrinks
            Set InsertRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

                Select Case Me.CmboServingsPerBottle.Value
                    Case "20"
                        Dim Dest As Range
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C1").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "30"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C2").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "200"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C3").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case Else
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("CoolDrinksInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C4").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                End Select

        'Income Statement CoolDrinks
            Set InsertRange = Worksheets("Income 
Statement").Range("CoolDrinksIncome")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2,   
iColumns))

            Set ItemEntryRange = Worksheets("Income 
Statement").Range("CoolDrinksIncome")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 2), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas

        'Profits CoolDrinks
            Set InsertRange = 
Worksheets("Profits").Range("CoolDrinksProfits")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Profits").Range("CoolDrinksProfits")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Offset(0, 2).Value = TxtPurchasePrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 3), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 3).PasteSpecial 
Paste:=xlPasteFormulas

Case "Beer and Cider"
    'Beer and Cider
        'Daily Sales BeerCider
            Set InsertRange = Worksheets("Daily 
Sales").Range("BeerCiderSales")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Daily 
Sales").Range("BeerCiderSales")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Deliveries BeerCider
            Set InsertRange = 
Worksheets("Deliveries").Range("BeerCiderDeliveries")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Deliveries").Range("BeerCiderDeliveries")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Total Inventory BeerCider
            Set InsertRange = Worksheets("Total 
Inventory").Range("BeerCiderInv")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BeerCiderInv")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

                Select Case Me.CmboServingsPerBottle.Value
                    Case "20"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BeerCiderInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C1").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "30"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BeerCiderInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C2").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "200"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BeerCiderInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C3").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                End Select

        'Income Statement BeerCider
            Set InsertRange = Worksheets("Income 
Statement").Range("BeerCiderIncome")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Income   
Statement").Range("BeerCiderIncome")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 2), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas

        'Profits BeerCider
            Set InsertRange = 
Worksheets("Profits").Range("BeerCiderProfits")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Profits").Range("BeerCiderProfits")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Offset(0, 2).Value = TxtPurchasePrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 3), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 3).PasteSpecial 
Paste:=xlPasteFormulas

Case "Bitters"
    'Bitters
        'Daily Sales Bitters
            Set InsertRange = Worksheets("Daily 
Sales").Range("BittersSales")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Daily 
Sales").Range("BittersSales")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Deliveries Bitters
            Set InsertRange = 
Worksheets("Deliveries").Range("BittersDeliveries")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Deliveries").Range("BittersDeliveries")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Total Inventory Bitters
            Set InsertRange = Worksheets("Total 
Inventory").Range("BittersInv")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1),  
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BittersInv")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

                Select Case Me.CmboServingsPerBottle.Value
                    Case "20"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BittersInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C1").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "30"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BittersInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C2").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                    Case "200"
                        Set ItemEntryRange = Worksheets("Total 
Inventory").Range("BittersInv")
                        Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
                        Set Dest = Range(ItemEntryRange.Offset(0, 2), 
ItemEntryRange.Offset(0, iColumns))
                        Range("C3").Copy
                        ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas, operation:=xlNone
                        ItemEntryRange.Offset(0, 2).AutoFill 
Destination:=Dest, Type:=xlFillValues
                End Select

        'Income Statement Bitters
            Set InsertRange = Worksheets("Income 
Statement").Range("BittersIncome")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Income 
Statement").Range("BittersIncome")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 2), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 2).PasteSpecial 
Paste:=xlPasteFormulas

        'Profits Bitters
            Set InsertRange = 
Worksheets("Profits").Range("BittersProfits")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Profits").Range("BittersProfits")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
            ItemEntryRange.Offset(0, 1).Value = TxtSalesPrice.Value
            ItemEntryRange.Offset(0, 2).Value = TxtPurchasePrice.Value
            ItemEntryRange.Range(ItemEntryRange.Offset(-1, 3), 
ItemEntryRange.Offset(-1, iColumns + 1)).Copy
            ItemEntryRange.Offset(0, 3).PasteSpecial 
Paste:=xlPasteFormulas

Case "Brandy"
    'Brandy
        'Daily Sales Brandy
            Set InsertRange = Worksheets("Daily 
Sales").Range("BrandySales")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = Worksheets("Daily 
Sales").Range("BrandySales")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value

        'Deliveries Brandy
            Set InsertRange = 
Worksheets("Deliveries").Range("BrandyDeliveries")
                iColumns = InsertRange.Columns.Count
            Set InsertRange = Range(InsertRange.Cells(2, 1), 
InsertRange.Cells(2, iColumns))
            InsertRange.Insert Shift:=xlDown
            Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, 
iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, 
iColumns))

            Set ItemEntryRange = 
Worksheets("Deliveries").Range("BrandyDeliveries")
            Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
            ItemEntryRange.Value = TxtNewItemName.Value
share|improve this question
2  
Answers are not for posting additional code that doesn't fit into the question. If there is too much code, then try to split it into separate questions if possible. If not, then it may not work on this site. –  Jamal♦ May 20 at 15:34
    
@Meringue90: A lot of the code blocks look like they are mostly copy-paste with slightly different values. It would benefit you to focus on one area of the code at a time. You may ask multiple questions with different sections as well. –  Phrancis May 20 at 15:45
    
I don't think we need all of the copy-pasta code. And I would advise against posting another question until this one is reviewed, too; odds are concepts will be applicable to all sections of the code. –  Mat's Mug♦ May 20 at 15:47
    
Two things I see right away. 1) You should be using OptionExplicit. 2) You should be using With blocks e.g. With InsertRange iColumns = .Columns.Count Set InsertRange=Range(.cells(2,1), .Cells(2, iColumns)) End With . –  CBRF23 May 21 at 20:03
    
Also, pick a naming convention and be consistent with it. iColumns is Hungarian notation while InsertRange and ItemEntryRange are kind of a bastardized Hungarian where the type is identified at the end of the name. I personally like Hungarian notation, but you don't see much love for it on this site ;) Also, camelCase is generally used over PascalCase. –  CBRF23 May 21 at 20:16

1 Answer 1

up vote 2 down vote accepted

I won't rework your whole code, because it's quite large, but I will try to give you an idea how to make it shorter:

DoCoolDrinks "Daily Sales", "CoolDrinksSales"
DoCoolDrinks "Deliveries", "CoolDrinksDeliveries"
DoCoolDrinks "Total Inventory", "CoolDrinksInv"

Sub DoCoolDrinks(ByVal strWorksheet As String, ByVal strRange As String)
    Set InsertRange = Worksheets(strWorksheet).Range(strRange)
    iColumns = InsertRange.Columns.Count
    Set InsertRange = Range(InsertRange.Cells(2, 1), InsertRange.Cells(2, iColumns))
    InsertRange.Insert Shift:=xlDown
    Range(InsertRange.Cells(1, 2), InsertRange.Cells(1, iColumns)).Copy Range(InsertRange.Cells(2, 2), InsertRange.Cells(2, iColumns))

    Set ItemEntryRange = Worksheets(strWorksheet).Range(strRange)
    Set ItemEntryRange = ItemEntryRange.Cells(2, 1)
    ItemEntryRange.Value = TxtNewItemName.Value
End Sub

That's the general idea. However, there are usually several ideas beginner macro developers overlook:

  • it's easier to use sophisticated functions than to write macro's
  • it's easier to use pivot tables than to write macro's
  • it's better to collect all related data in one table, then to spread everything over several worksheets.
  • it's useful to use named ranges (like "CoolDrinksSales") for everything in your sheet, instead of relying on A1, A2, etc
  • it's better in Excel macros to work with relative references instead of named ranges. So use properties like 'currentregion', 'selection', and 'activecell'.
share|improve this answer
    
BRILLIANT! Works perfectly! Thank you! You have saved me DAYS. The internet here is so bad, it would have taken me days to google a solution to that. Thank you so much. –  Meringue90 May 23 at 10:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.