Use variable to define range name

[Paul Sansom]

Hi
I have a simple macro that I copy paste a range defined from a name
The range in the Macro is "Recipe1". I would like this to be defined from a cell that varies (from my Combo Box)
Recipe2 Recipe3 etc. I have already defined these ranges but....

I am struggling with the code to replace the .Range("Recipe1") at the begining of the macro below
The vaiable range text to repalce above is at cell $R$4 on "sheet3"

Does this make sense??? and can anyone help please???
Paul

Sub SampleRecipeLoad()
'
' SampleRecipeLoad Macro
'   Load Chosen Recipe into the raws selection area
'

'   Load Chosen Recipe
    Workbooks("Recipe.xlsm").Worksheets("Sample Recipes").[B]Range("Recipe1").C[/B]opy _
        Destination:=Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("C9")
    
'   Set Chocolate type to Milk and counrty check to EU
    Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("Input_ChocType") = "Milk"
    Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("Country_Output") = "EU"
    
'   Close the Selection Panel Window
    Unload UserForm2
    
End Sub

 

[Jonmo1]

Assuming Sheet3 R4 = Recipe1
Try

Dim MyRange As String
MyRange = Sheets("Sheet3").Range("R4").Value
    Workbooks("Recipe.xlsm").Worksheets("Sample Recipes").Range(MyRange).Copy _
        Destination:=Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("C9")

 

[JimM]

Sub SampleRecipeLoad()
'
' SampleRecipeLoad Macro
' Load Chosen Recipe into the raws selection area
'

Dim recipeName
RecipeName = Sheets("Sheet3").Range("R4").Value

' Load Chosen Recipe
Workbooks("Recipe.xlsm").Worksheets("Sample Recipes").Range(RecipeName).Copy _
Destination:=Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("C9")

' Set Chocolate type to Milk and counrty check to EU
Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("Input_ChocType") = "Milk"
Workbooks("Recipe.xlsm").Worksheets("Recipe").Range("Country_Output") = "EU"

' Close the Selection Panel Window
Unload UserForm2

End Sub

 

[Paul Sansom]

Thanks JonMo
Worked well , just had to set dim Myrange without string datatype
Dim MyRange

 

[Paul Sansom]

Perfect
Really big thanks Jim

 

[Jonmo1]

Glad to help, thanks for the feedback.