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 RangeDim ItemEntryRange As RangeDim iColumns As IntegerSelect Case Me.CmboItemType.ValueCase 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:=xlPasteFormulasCase 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:=xlPasteFormulasCase 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:=xlPasteFormulasCase 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
Inserting row into named range code
beginner;vba;excel
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, CoolDrinksSalesDoCoolDrinks Deliveries, CoolDrinksDeliveriesDoCoolDrinks Total Inventory, CoolDrinksInvSub 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.ValueEnd SubThat'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'sit's easier to use pivot tables than to write macro'sit'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, etcit's better in Excel macros to work with relative references instead of named ranges. So use properties like 'currentregion', 'selection', and 'activecell'.