I am writing some VBA which copies results in a column and pastes them in another column. The problem is that there are 5 different scenarios that I want to do this for so that I can compare each one and in order to change the scenario I need to change an Input cell (C2) to a number 1-5. I have written a code to do this but it is pretty long winded and if want another scneario added then I will need to go in and add some more code. I was wondering if there was a quicker way than what I have done already (see below for my code)
Worksheets("XXX").Range("C2") = 1
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("D4:D25").PasteSpecial xlPasteValues
Worksheets("XXX").Range("C2") = 2
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("E4:E25").PasteSpecial xlPasteValues
Worksheets("XXX").Range("C2") = 3
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("F4:F25").PasteSpecial xlPasteValues
any ideas?
Worksheets("XXX").Range("C2") = 1
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("D4:D25").PasteSpecial xlPasteValues
Worksheets("XXX").Range("C2") = 2
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("E4:E25").PasteSpecial xlPasteValues
Worksheets("XXX").Range("C2") = 3
Worksheets("XXX").Range("B4:B34").Copy
Worksheets("XXX").Range("F4:F25").PasteSpecial xlPasteValues
any ideas?