Autofill dynamic range across sheet

[coolhandphil]

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]

What's in all those variables?

[coolhandphil]

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]

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]

To my understanding of your reply, my code should therefore be written as:

[VBA] 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[/VBA]

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

[Bob Phillips]

In this case, as the ranges are not contiguous, I would think you need to copy from one to the other.