VBA copy range names that are held in a list to another set of ranges in another list

[Bubbachew]

Hello!

This is my first post, so I will try and be as comprehensive as I can. I really have tried to look through many back posts to find an answer, but I'm struggling to find the right post, sorry!

To start with, I have a drop down list to allow the user to choose a report to be created, I then have a macro that will run depending on the report choice, fine.

The list contains a set of range names that I would like the macro to copy from and then there is another list of range names that I would like it to copy to until complete.

I have done this I guess you would call it 'long hand' and the overall code is huge!

I am trying to tidy up my code using some for next loops.

The code that will be in the loop, will look like this -

    Sheets("Lookups").Select

    Range("I3").Select '' i would like this to look at each range name held in the range list.

    Selection.Copy
    Sheets("P&L - Divisional").Select
    Range("DivName").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.Goto Reference:="Act"
    Application.CutCopyMode = False
    Selection.Copy

    Sheets("Report " & Range("Currency").Value).Select '' this just decides which report page to copy to

    Range("C8").Select '' I would like this to also look at range names in another list
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("P&L - Divisional").Select
    Range("DivName").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Report " & Range("Currency").Value).Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("P&L - Divisional").Select
    ActiveWindow.SmallScroll Down:=-3
    Application.Goto Reference:="Bud"
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Report " & Range("Currency").Value).Select

    Range("J8").Select '' I would like this to also look at range names in another list

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False

Would very much appreciate some help, thank you!

M