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

try using something like

mx=range("a10")
range("f8:f12").autofill destination:=range(cells(8,"f"),cells(12,mx))

--
Don Guillett
SalesAid Software

"travis" wrote in message
ps.com...
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

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

Tom Ogilvy wrote:
With Range("startingcolumn")
.AutoFill Destination:= _
.Resize( ,Range("projectionyears").Value), _
Type:=xlFillDefault
End With

Thanks Tom, that's a very elegant solution.

Travis