PDA

View Full Version : [SOLVED] Using a Variable in a named range



DaveGib
09-06-2017, 08:26 AM
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
09-06-2017, 08:31 AM
Try using:


Application.Range(sc).Copy

instead of your two Select and Copy lines.

DaveGib
09-06-2017, 08:39 AM
Wow!!!..... Thanks Aflatoon - that works perfectly!!!
Thanks very much!!!
Dave

mdmackillop
09-06-2017, 10:42 AM
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
09-06-2017, 10:49 PM
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
09-07-2017, 01:29 AM
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
09-07-2017, 01:43 AM
Hi mdmackillop,
Thanks a stack for these two options, - they both work perfectly!!!:bow::bow::bow:
I think the second option seems to be quicker...... you can hardly see any transition
THANKS again!!!! :thumb

snb
09-07-2017, 02:50 AM
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
09-07-2017, 03:04 AM
Wow!!!......... it keeps getting better and better!! :clap:
Thanks Snb
I have learn't a lot from this post - Thanks very much to you all....... GREATLY appreciated... you are ALL gurus. :bow: :bow: :bow:
3 great snippets that I have saved........