Macro to copying values in named ranges

[Gregg Wolin]

I have a model with over 100 individual user inputs. My "Input" worksheet is set up so that the assumptions are all contained within a single column with over 200 rows so that each time the assumptions change (even slightly), I can simply copy the column to another area of the worksheet I reserved for "Scenarios". I can then simply paste the values from any scenario back into the inputs column.

It works fine, but the UI would be much better if I could use more screen real estate in a dashboardy format. I think I need a macro to copy and paste the values from all the Named ranges (cells) into a holding sheet or area (not unlike my current Scenario area). It seems like there would be an easy way to copy all the values from named ranges (just like excel lets you do a "print list" to see all the named ranges and their cell locations.

Any strategic ideas?
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Not sure what you mean. But to loop through all named ranges in a workbook and copy value to specified sheet. You'd do something like...

This assumes all named ranges are single column.

Sub Test()
Dim myName As Name

i = 1
'Change Sheet1 to the sheet you need to copy values to
For Each myName In ThisWorkbook.Names
    Sheet1.Cells(1, i) = myName.Name
    Range(myName.Name).Copy Sheet1.Cells(2, i)
    i = i + 1
Next

End Sub