I am wanting to construct a Purchase Order template in excel - by selecting on a machine model from a drop down list (there are multiple models), I would like to copy in a standard set of accessories and costings associated with the model. The Purchase Order may have multiple purchases so I have set up 5 drop down boxes down the page.
After setting up name ranges to associate the accessories and prices with each model, I recorded the macro. The macro worked perfectly apart from the fact that the Name Range does not change when a new model from the drop down box is selected. Here is an example:
Sub Macro1()
'
' Macro1 Macro
' To populate Purchase Order with standard accessories and costings
'
' Keyboard Shortcut: Ctrl+q
'
Range("A7").Select
Selection.Copy
Application.Goto Reference:="iRADVC5030"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A18").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iR1024iF"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
ActiveWindow.SmallScroll Down:=12
Range("A29").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRADV6075"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-30
Range("A1").Select
ActiveWindow.SmallScroll Down:=27
Range("A40").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRC3080i"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Range("A1").Select
ActiveWindow.SmallScroll Down:=39
Range("A51").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRADVC5051"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Range("A1").Select
End Sub
Can someone please advise how a macro can identify different name references in a drop down box?
After setting up name ranges to associate the accessories and prices with each model, I recorded the macro. The macro worked perfectly apart from the fact that the Name Range does not change when a new model from the drop down box is selected. Here is an example:
Sub Macro1()
'
' Macro1 Macro
' To populate Purchase Order with standard accessories and costings
'
' Keyboard Shortcut: Ctrl+q
'
Range("A7").Select
Selection.Copy
Application.Goto Reference:="iRADVC5030"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A18").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iR1024iF"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
ActiveWindow.SmallScroll Down:=12
Range("A29").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRADV6075"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-30
Range("A1").Select
ActiveWindow.SmallScroll Down:=27
Range("A40").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRC3080i"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Range("A1").Select
ActiveWindow.SmallScroll Down:=39
Range("A51").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="iRADVC5051"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sales Purchase Order").Select
Range("B51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Range("A1").Select
End Sub
Can someone please advise how a macro can identify different name references in a drop down box?