Dragging a range of cells
With Range("startingcolumn")
.AutoFill Destination:= _
.Resize( ,Range("projectionyears").Value), _
Type:=xlFillDefault
End With
--
Regards,
Tom Ogilvy
"travis" wrote:
I am working on a spreadsheet with a variable number of columns. It is
a financial projection spreadsheet (retirement planning) of high
complexity.
With 100 columns (i.e. a 100 year projection), the spreadsheet is both
huge and slow. I don't think I always want only 100 columns though.
I'm trying to create a version which starts with a more manageable
number of years, i.e. 5, which can then be extended with a macro. This
way I would have a file of only a few hundred k to distribute, which
would run quickly, and if someone wants to create a 10 megabyte 100
column monster out of it, its their choice.
If I want to drag the range F8:F12 right ten columns, I would use this
code:
Range("$F$8:$F$12").Select
Selection.AutoFill Destination:=Range("F8:O12"), Type:=xlFillDefault
Easy enough. A recorded macro does that.
But I want to drag by a variable number of columns.
If I want to project for 10 years, I would have a cell with the number
10 in it and a button next to it saying "Go" or something.
My number of years to project is in a cell specified by
Range("projectionyears").Value
And I don't actually want to specify my range to extend from as
Range("$F$8:$F$12"), the range was actually called
Range("startingcolumn").
So what I want to know, is how to take the range
Range("startingcolumn") and extend this right by
Range("projectionyears").Value columns.
Travis
|