Using a Variable in a named range

[DaveGib]

Hi All......

Can someone please help me with my problem? - I have a worksheet that is set out as a form for a user to fill in.
Near the top of the form there is a validation list that the user must select a code from.
On a seperate hidden sheet I have several ranges of data, each range has its own code which I have defined with a named range that corresponds to the code in the validation list.
The object is that depending on the code selected by the user, the form is populated with a range that corresponds to the code.
I can get the macro to run if I hard code the Input code, but I cannot get it to run if I use a variable.
Thanks in advance
Dave

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    Dim sc As Range    '** a variable to hold the contents of J4
    
'  ** Check if Cell J4 is updated
  If Not Intersect(Target, Range("J4")) Is Nothing Then
  
   Set sc = Range("J4")
    
  Sheet3.Visible = xlSheetVisible    '** The hidden sheet with the ranges on
  Sheet3.Select
  
 ' [H1N].Select  '** IF I HARD CODE THE DETAILS IN J4 IT WORKS - H1N is a code in the list selected by the user
  [" & sc & "].Select   '** THIS FAILS HERE - ASKING FOR AN OBJECT
  Selection.Copy
  
  Sheet10.Select
  
  Range("A11").PasteSpecial xlPasteValues
      
  End If
  
  Application.ScreenUpdating = True

[Aflatoon]

Try using:

Application.Range(sc).Copy

instead of your two Select and Copy lines.

[DaveGib]

Wow!!!..... Thanks Aflatoon - that works perfectly!!!
Thanks very much!!!
Dave

[mdmackillop]

or

Private Sub Worksheet_Change(ByVal Target As Range)     '  ** Check if Cell J4 is updated
    If Not Intersect(Target, Range("J4")) Is Nothing Then
        Sheet10.Range("A11").Value = Target.Value
    End If
End Sub

[DaveGib]

Hi mdmackillop, - thanks very much for your response, I appreciate it! - I am very interested in learning new ways and improving my vba, and your reply looks very concise and impressive, but when I run the code, it just puts the contents of J4 into A11 and not the range that J4 is supposed to refer to. - It should fill A11:H16 with data specific to the code in J4.
I don't know if it makes a difference, but the ranges that are referred to are on a different sheet ( sheet3)
the ranges that are being referred to have Vlookup formulas in them, hence the need to paste values when copying.
Regards

[mdmackillop]

2 options (if i have it right this time)

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim sc As Range '** a variable to hold the contents of J4
     
     '  ** Check if Cell J4 is updated
    If Not Intersect(Target, Range("J4")) Is Nothing Then
        Sheet3.Range(Target).Copy
        Sheet10.Range("A11").PasteSpecial xlPasteValues
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
     '  ** Check if Cell J4 is updated
    Dim r As Range
    If Not Intersect(Target, Range("J4")) Is Nothing Then
        Set r = Sheet3.Range(Target)
        Sheet10.Range("A11").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
    End If
End Sub

[DaveGib]

Hi mdmackillop,
Thanks a stack for these two options, - they both work perfectly!!!
I think the second option seems to be quicker...... you can hardly see any transition
THANKS again!!!!

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$J$4" Then
    With Sheet3.Range(Target)
      Sheet10.Range("A11").Resize(.Rows.Count, .Columns.Count) = .Value
    End With
  End If
End Sub

[DaveGib]

Wow!!!......... it keeps getting better and better!!
Thanks Snb
I have learn't a lot from this post - Thanks very much to you all....... GREATLY appreciated... you are ALL gurus.
3 great snippets that I have saved........