Copy and Paste Macro with Named Ranges

Very new to VBA so looking for some help shortening the below code - I know I should be using a loop but I can't figure out how to do so with the Scenario_Paste named ranges.

Any pointers, greatly appreciated!


Range("Scenario_No.").Value = 1
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste1").PasteSpecial xlPasteValuesAndNumberFormats

Range("Scenario_No.").Value = 2
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste2").PasteSpecial xlPasteValuesAndNumberFormats


Range("Scenario_No.").Value = 3
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste3").PasteSpecial xlPasteValuesAndNumberFormats


Range("Scenario_No.").Value = 4
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste4").PasteSpecial xlPasteValuesAndNumberFormats



Range("Scenario_No.").Value = 5
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste5").PasteSpecial xlPasteValuesAndNumberFormats



Range("Scenario_No.").Value = 6
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste6").PasteSpecial xlPasteValuesAndNumberFormats



Range("Scenario_No.").Value = 7
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste7").PasteSpecial xlPasteValuesAndNumberFormats



Range("Scenario_No.").Value = 8
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste8").PasteSpecial xlPasteValuesAndNumberFormats


Range("Scenario_No.").Value = 9
Application.CalculateFull

Range("Scenario_Copy").Copy
Range("Scenario_Paste9").PasteSpecial xlPasteValuesAndNumberFormats


Application.CutCopyMode = False


End Sub

Great thank you for your help! Really appreciate it.

One element doesn't seem to be running correctly. After each scenario is run, I want the results to be pasted only in that applicable scenario and not in all scenario ranges - currently, the results from scenario 9 (last scenario) are being pasted into all the paste ranges. Do you know a quick fix for that?

Originally Posted by GavinF

Great thank you for your help! Really appreciate it.

One element doesn't seem to be running correctly. After each scenario is run, I want the results to be pasted only in that applicable scenario and not in all scenario ranges - currently, the results from scenario 9 (last scenario) are being pasted into all the paste ranges. Do you know a quick fix for that?

Nevermind, it does run correctly. I ran the wrong sub - thanks again, it's very helpful