PDA

View Full Version : Autofill issue



ppataki
03-22-2011, 02:15 AM
Dear All,

I have prepared the below code but at the last line I get an 1004 error code
Could somebody please advise?

Dim SourceWB As Workbook
Dim LR As Long
Set SourceWB = Workbooks("hatósági sablon.xlsx")
LR = SourceWB.Sheets("sablon").Range("a" & Rows.Count).End(xlUp).Row
SourceWB.Sheets("sablon").Range("b500").End(xlUp).AutoFill Destination:=SourceWB.Sheets("sablon").Range(Range("b500").End(xlUp) & ":b" & LR)

Many thanks in advance

Bob Phillips
03-22-2011, 02:24 AM
Untested, but try this



Dim SourceWB As Workbook
Dim LR As Long
Set SourceWB = Workbooks("hatósági sablon.xlsx")
With SourceWB.Sheets("sablon")

LR = .Range("a" & Rows.Count).End(xlUp).Row
.Range("B500").End(xlUp).AutoFill Destination:=.Range("B500:B" & LR)
End With

ppataki
03-22-2011, 02:27 AM
I still get the same 1004 error at the last line (.range...)
(using Excel 2007)

Bob Phillips
03-22-2011, 03:30 AM
Okay, try again



Dim SourceWB As Workbook
Dim LR As Long
Set SourceWB = ActiveWorkbook 'Workbooks("hatósági sablon.xlsx")
With SourceWB.Sheets("sablon")

LR = .Range("A" & Rows.Count).End(xlUp).Row
If LR > 500 Then .Range("B500").AutoFill Destination:=.Range("B500:B" & LR)
End With