Excel/Scenario Automation VBA - Clarifiation
I actually 4 worksheets.
The missing one is called Parameters
The column set for each scenario in "Scenario" sheet is pasted as values into a box C7:C10 in "Parameters"
The Calcs sheet 2 ranges then show the results for this scenario
Apologies for any confusion
In Scenario I assume your data is in A1:JJ3
You don't say.
I paste 3 vertical cells (single column) in C7 (so C7:C9)
This worked as I expected based on my understanding of your requirements and given my stated assumptions.
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Dim cell As Range, rvar As Range, res1 As Range, res2 As Range
Dim r As Range
Set sh1 = Worksheets("Parameters")
Set sh2 = Worksheets("Calcs")
Set sh3 = Worksheets("Scenario")
Set sh4 = Worksheets("All")
Set rvar = sh3.Range("A1").Resize(1, 270)
Set res1 = sh2.Range("A389:D442")
Set res2 = sh2.Range("CL389:CV442")
For Each cell In rvar
sh1.Range("C7").PasteSpecial Paste:=xlPasteValues, Transpose:=False
Set r = sh4.Cells(sh4.Rows.Count, "A").End(xlUp).Offset(2, 0)
r.Offset(0, 4).PasteSpecial Paste:=xlPasteValues
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here