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