Reference cells containing title of named ranges to be used in VBA

[dmyron]

I’m creating a macro that will select multiple named ranges at one time. I’ve got it to work using “Application.Goto” but only when manually entering the named range title.

Sub Appl_Goto()
    Application.Goto Reference:=Worksheets("Funding(4)").Range("MarPE,MarRW,MarCN"), Scroll:=True
End Sub

I need to make the range more dynamic and allow for user selection of those named ranges. What I’ve done is created a formula in sheet “UPDATES” that uses input from a user dropdown menu selection to return three different named ranges. User selects month to be updated (Jan-Dec), formula in cells A25, A26 and A27 (using CONCATENATE) produces three different named range titles (ranges that are already defined and all on tab “FUNDING(4)”). For example, if the user selects the month "Mar", the three cells below populate with "MarPE", "MarRW", and "MarCN".

How do I force the “Application.Goto” code to use these three cells as the named range titles and simultaneously select the three ranges? Is there a better option available that I'm unaware of? This is to be phase one of what will be a two-phase macro - this phase selects three ranges, then phase two will either copy/paste special those ranges in place or break links. Perhaps there's a better way to do both at once? Thanks All.

 

[jim may]

Welcome to the Board...

I think you are currently selecting 3 non-contiguous ranges. These 3 ranges can be selected, but you CANNOT be Pasted in one process. I'd pursue using a loop (of 3) to individually copy and paste.

 

[dmyron]

Thanks for the welcome! I will pursue using a loop to copy and paste the three ranges individually. Thank you for that recommendation.

As far as making the selection of the three ranges dynamic, do you have any thoughts? This is my primary hurdle at the moment. If I can't get beyond this, I will have to change my approach entirely.
My code works when I type "MarPE","MarRW","MarCN" (as shown above), but if I try to reference three cells with the formula value of "MarPE", "MarRW","MarCN", respectively, I get an error.

 

[jim may]

On My Sheet1 I created 3 range_Names: RN_A (Yellow), RN_B (Blue) and RN_C (Brown). Then ran the below code with and existing empty Sheet2 ... Just providing you a sample...

Excel 2012
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9
10
11
12
Sheet1

Sub Foo1()
Application.Goto Reference:=Worksheets("Sheet1").Range("RN_A,RN_B,RN_C") ', Scroll:=True

' MsgBox Selection.Address
a = 4
b = 3
For i = 1 To Selection.Areas.Count
    Selection.Areas(i).Copy
    With Sheet2
        .Cells(a, b).PasteSpecial xlPasteAll
    End With
    a = a + 3
    b = b + 2
Next i
Application.CutCopyMode = False
End Sub

 

[dmyron]

This was very helpful. Thanks. It's probably a very ugly code to a VBA guru such as yourself, but this is what ended up working for me. I was even able to effectively "undo" the hardcoding performed here by reusing the code to copy and paste formulas from a backup tab, essentially restoring the columns to their original state.

Sub Lock_Copy_Paste_Next()
Application.ScreenUpdating = False
    Application.Goto Reference:=Worksheets("Funding").Range(Worksheets("Lock Month").Range("MmmPE")), Scroll:=True
    a = 4
    b = 3
For i = 1 To Selection.Areas.Count
    Selection.Areas(i).Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    a = a + 3
    b = b + 2
    Next i
    Application.Goto Reference:=Worksheets("Funding").Range(Worksheets("Lock Month").Range("MmmRW")), Scroll:=True
    a = 4
    b = 3
For i = 1 To Selection.Areas.Count
    Selection.Areas(i).Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    a = a + 3
    b = b + 2
    Next i
    Application.Goto Reference:=Worksheets("Funding").Range(Worksheets("Lock Month").Range("MmmCN")), Scroll:=True
    a = 4
    b = 3
For i = 1 To Selection.Areas.Count
    Selection.Areas(i).Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    a = a + 3
    b = b + 2
    Next i
Application.ScreenUpdating = True
End Sub