how to paste contents of named range based on active cell contents using VBA

0
votes

I am trying to paste the contents of a Named Range, based on the active cell contents (which is from a data validation dropdown).

The active cell contents is equal to the Named Range name.

There are 40 odd Named Ranges in the sheet, and I want to paste them into a column by selecting a specific cell and calling the macro.

I can create the variable and assign the active cell contents to the variable, but I don't see how to assign the variable to the name of the named range and paste the contents.

I have tried the following code:

Sub PrintRange()
'
' PrintRange Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Dim LO As Variant
    Set LO = ActiveCell
    Application.Goto Reference:="LO"
    Selection.Copy
    Sheets("Test page").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
End Sub

Could anyone give me some advice?

2
vbaexcel
Have you tried anything – Linga
yes sorry I have it on other computer, in a bit of a rush, – Jonathan
I am copying by eye - – Jonathan
dim LO As Variant; Set LO = ActiveCell; Application.Goto Reference = "LO"; Selection.Copy; ActiveCell.Offset etc; ActiveSheet.Paste; End Sub – Jonathan
What do you think. I have tried several data types etc. I am sure it is how I am assigning the LO variable to the Named Range name... – Jonathan

2 Answers

0
votes

Hope this will help you :)

Sub linga()
Dim srcRange As Range, destRange As Range
'Replace Num with your named range
Set srcRange = Range("Num")
Set destRange = ActiveCell.Resize(srcRange.Rows.Count, srcRange.Columns.Count)

destRange.Value = srcRange.Value
End Sub
0
votes

This worked perfectly at the end of the day:

Sub PrintRange() ' ' PrintRange Macro ' ' Keyboard Shortcut: Ctrl+Shift+L ' LO = ActiveCell Application.Goto Reference:=(LO) Selection.Copy Sheets("Data Entry").Select ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False

End Sub