Hello CandyK,

To use this macro code the choices need to be the names of the Named
Ranges. I can alter the code if you decide to use more descripitive and
user friendly names. Copy the macro into a standard VBA code module and
then assign the macro to your Drop Down Box. The data from the selected
Named Range will be copied into cells G7:L34 on the worksheet the Drop
Down Box is on.

To Add a Code Module:
1) Copy the macro code to the clipboard using Ctrl + C.
2) Open the Excel Workbook the macro will be used in.
3) Press Alt+F11 to launch the VBA Editor.
4) Press Alt+I to view the Insert Menu.
5) Press M to insert a Module into your Workbook.
6) Press Ctrl+V to Paste the code into the Module.
7) Press Ctrl+S to Save the Macro.
8) Press Alt+Q to Quit the VBA Editor and return to Excel.



Code:
--------------------

Public Sub DropDownMacro()

Dim Choice As String
Dim DestinationRange As Range
Dim DropDown
Dim NamedRange As Range
Dim WksCells As String
Dim WksName As String

DropDown = Application.Caller
Set DestinationRange = ActiveSheet.Range("G7:L34")

With ActiveSheet.Shapes(DropDown).ControlFormat
Choice = .List(.ListIndex)
End With

Set NamedRange = ThisWorkbook.Names(Choice).RefersToRange

With NamedRange
WksCells = .Address
WksName = .Parent.Name
End With

DestinationRange.ClearContents
Worksheets(WksName).Range(WksCells).Copy Destination:=DestinationRange.Cells(1, 1)

End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=548311