PDA

View Full Version : Autofill dynamic range across sheet



coolhandphil
03-08-2007, 07:34 AM
Hi,

I need to autofill a range of cells x number of columns to the right.

To give some detail:

I have a table of data which shrinks and grows in length. I also want to give the user the option to change the number of months of data to display.

I have managed to select the existing data that I want to drag across. However I keep getting an error when I try to set it to the new destination.

My code is as follows:




Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault


Regards,

Phil

Bob Phillips
03-08-2007, 07:55 AM
What's in all those variables?

coolhandphil
03-08-2007, 08:15 AM
Hi,

The variables...

LastReportColumn = 9
LastReportRow = 87
ReqMonths = 8
ActMonths = 4

ReqMonths is the number of months the user has requested to see.
ActMonths is the number of months currently being displayed. (Req - Act) = the number of additional columns to add.

LastReportRow is the last row in the report/table.

LastReportColumn is the last column in the table. (I minus one because after the last column of monthly data is a sum field.) I plan to keep it simply and autofill over the top of my sum data. I will then use code to add a new sum column after. Hope that makes sense!

Phil

Bob Phillips
03-08-2007, 08:22 AM
By my calculation, you are trying to autofill from

?Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).address
$H$7:$H$87

to

?Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5)).Address
$M$7:$M$87

If you look at help for autofill, for destination it says

Destination Required Range object. The cells to be filled. The destination must include the source range.


Therein lies your problem.

coolhandphil
03-08-2007, 09:15 AM
To my understanding of your reply, my code should therefore be written as:

DestinationRange = Sheets("Report").Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).AutoFill Destination:=(DestinationRange), Type:=xlFillDefault

But this gives the error "AutoFill method of Range class failed"

Bob Phillips
03-08-2007, 10:23 AM
In this case, as the ranges are not contiguous, I would think you need to copy from one to the other.