0

I'm working on a business case with a growing number of scenarios, I could do all these in scenario manager but that's not convenient right now.

What I'm trying to do is the following: I've got a list of names for the given scenarios, these should be pasted to an input field which then runs the scenario, the results should then be copied to a location specified for that scenario.

The current code does it without any issues but it feels 'sluggish' to me because I need to make a different block of code for each scenario:

    Sheets("Output").Select
Range("G7").Select
ActiveCell.FormulaR1C1 = "All stores"
Sheets("Stuurgroep").Select
Range("N4:N18").Select
Selection.Copy
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Output").Select
Range("G7").Select
ActiveCell.FormulaR1C1 = "Quartile 1"
Sheets("Stuurgroep").Select
Range("N4:N18").Select
Selection.Copy
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

In which G7 is the place for the scenario, "all stores" in this case is the first scenario, N4:N18 are the results of that scenario and C4 is the place for those results, with the results of the next scenario being pasted into D4.

I've tried the following code to create the list to paste into G7 but it doesn't work:

    Set ListScenarios = ActiveWorkbook.Sheets("RefTables").Range("B3:B11")

For Each cell In ListScenarios
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Range("G7").Select
ActiveSheet.Paste

Sheets("Stuurgroep").Select
Range("N4:N18").Select
Selection.Copy
Call Paste
Next cell

    Sub Paste()

Set Destination = ActiveWorkbook.Sheets("Stuurgroep").Range("C4:K4")
For Each cell In Destination
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Next cell

Help would be much appreciated!

Bob Janson
  • 115
  • 5
  • So you are trying to copy a rage of cells into a new range? – Mikhail Zakharov Jun 19 '18 at 06:42
  • Not exactly, I'm trying to have the macro run the scenarios in the range one by one and then pasting the results after each run in a range of cells. Keeping the results from each run. – Bob Janson Jun 19 '18 at 07:05
  • It is primarily sluggish because of .Select. [Get rid of that .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Use With statements to work with your sheets. – QHarr Jun 19 '18 at 07:12
  • Thanks but that doesn't solve the main problem of having to write multiple blocks of code or not, I'll look at using With statements for the rest of the code! – Bob Janson Jun 19 '18 at 07:43

0 Answers0