Close on VBA Autofill

[aivoryuk]

Hi I have th following VBA code

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("chtname").AutoFill Destination:=Range("B1:B" & lr)
End Sub

What this does is autofill the cells in column B (which is named range chtname) based on the range of column A.

This works as it is but rather than use the range A2000 I want to use another named range "chtfirst" as the reference column.
I also can't get the VBA to work ("B1:B" & lr) using the named range "chtname"

Any ideas?

 

[GlennUK]

I must say I am lost. How can you autofill on just one value? With only one cell doesn't Excel just copy the cell. I thought autofill needed at least 2 cells to autofill down a column or across a row.

Yes, but copying a cell is sometimes what is required, for example, when it's a formula. Do you ever type in a formula in a cell in the top row of a table, and double-click the fill handle? That autofills the formula down as far as is required.

 

[Dartagnan]

Have you gotten this code to work?

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("B1").AutoFill Destination:=Range("B1:B" & lr)
End Sub

 

[Dartagnan]

"Yes, but copying a cell is sometimes what is required, . . ."

Good point. Hadn't thought of that.

 

[aivoryuk]

Have you gotten this code to work?

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("B1").AutoFill Destination:=Range("B1:B" & lr)
End Sub

Hi everyone thanks for the responses.

In answer to the above yes this does work.

On my spreadsheet where the data is imported I am using named ranges so that the data is auto updated and secondly any formula calculations across the range is much quicker that just making the range 50000 (its a big spreadhseet)

with the above I can use column A but in my sheet it is a named range so I wanted to get this VBA to work on named ranges rather than hardcoding ranges.