ExcelBanter - Autofill Dynamic Range of Cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Autofill Dynamic Range of Cells (https://www.excelbanter.com/excel-programming/446977-autofill-dynamic-range-cells.html)

Autofill Dynamic Range of Cells

I'm trying to do an autofill on a dynamic range of cells, and I'm having some trouble. Here is what I have so far:

Code:

Range("CR2:DC2").Select

i = Selection.End(xlDown).Row

Intersect(Range("CR:DC"), Rows(i)).Select



LastRow = ActiveSheet.Range("A1").End(xlDown).Row

        Selection.Autofill Destination:=Range( ? & LastRow)

I'm not sure what to put as my starting range in the autofill. The starting row value is stored in "i", just not the range of cells that I want to autofill. I tried putting that intersect statement in there, but it gave me a "type mismatch" error. Does anyone know what to do? Thanks!

Autofill Dynamic Range of Cells

Hi Keri,

This worked for me:

LastRow = ActiveSheet.Range("A1").End(xlDown).Row
Range("CR2:DC2").AutoFill Destination:=Range("CR2:DC" & LastRow)

-Ben

Autofill Dynamic Range of Cells

Hi

I maybe on the wrong track here but try this.

Sub Copy_myRng()

Dim SS As Worksheet 'Source
Dim TS As Worksheet 'Target
Dim mySrng As Range
Dim myTrng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set SS = Sheets("Sheet1")
Set mySrng = SS.Range("CR2:DC2")
Set TS = Sheets("Sheet2")
Set myTrng = TS.Range("A1")

With mySrng
.Copy Destination:=myTrng
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

HTH
Mick.