I have a model that produces multi-year financial output for a product.
The model has an input to change the product under review. I need to
create summary reports based on this financial output for a number of
products, which gets a little tricky as far as I can tell. Originally
I was using a two-way data tables with one axis as the product input
values and the other axis as the values I need for the output, i.e. Y1
Revenue, Y2 Revenue, etc. I was accomplishing this using choose
function for the output axis. However, this involved some manual
copying and pasting and performed somewhat slowly, so I thought VBA
would do the trick. With the help of some folks in this forum, I've
created the following code, which appears to work, but is *extremely*
slow -- even slower than the data table solution. Any suggestions on
how to improve the speed? Here's the code:


Sub ScenOut()
Dim rngNames As Range, rngScenInput As Range
Dim rng1 As Range
Dim cell As Range, cell1 As Range
Dim nm As Name
Dim c As Integer, r As Integer
Dim iMaxScen As Integer
Dim wks As Worksheet

Set wks = Worksheets("Output")
wks.Range("A3").Activate

Set rngNames = Range(Application.Names!OutputNames)
Set rngScenInput = Range(Application.Names("Product.Input"))

c = 1
iMaxScen = 10

Do While c <= iMaxScen
rngScenInput.Value = c
For Each cell In rngNames
r = 0
Set rng1 = Range(cell.Value)
For Each cell1 In rng1
r = r + 1
wks.Cells(r, c).Value = cell1.Value
Next cell1
Next cell
Loop
End Sub