Auto Fill Error

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
Hi

Would someone be able to help why this doesn't work?

VBA Code:
Sub Macro5()
'
' Macro5 Macro
'
Dim LR As Long, LR2 As Long, LR3 As Long, code As String

Sheets("Core Data").Select

LR = Range("B" & Rows.Count).End(xlUp).Row
LR2 = Range("R" & Rows.Count).End(xlUp).Row
LR3 = (Range("R" & Rows.Count).End(xlUp).Row) + 1
code = "R" & LR3 & ":V" & LR

    
    Range("R" & LR2 & ":V" & LR2).Select
    Range("V" & LR2).Activate
    Selection.AutoFill Destination:=Range(code), Type:=xlFillDefault
    Range(code).Select

End Sub

I'm importing data into first empty row in cells A:P and then autofill the formulas from R:V. However I get the following error:
Excel VBA Runtime Error 1004 - "Autofill Method of Range Class Failed”


Any help would be great.

Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,466
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub heffo()
   Dim Lr As Long
   With Sheets("Core Data")
      Lr = .Range("B" & Rows.Count).End(xlUp).Row
      With .Range("R" & Rows.Count).End(xlUp)
         .Resize(Lr - .Row + 1, 5).FillDown
      End With
   End With
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,050
Office Version
  1. 2019
Platform
  1. Windows
You have a lot of code that you don't need, but nothing stands out as the potential cause of your error.

Are any of the formulas in merged cells?
 

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
How about
VBA Code:
Sub heffo()
   Dim Lr As Long
   With Sheets("Core Data")
      Lr = .Range("B" & Rows.Count).End(xlUp).Row
      With .Range("R" & Rows.Count).End(xlUp)
         .Resize(Lr - .Row + 1, 5).FillDown
      End With
   End With
End Sub
Thanks a million that work perfectly, I want this to copy down the formula which it does and then afterwards copy all cells and past special over the cell with values that the original formulas returned, what would be the best way to do this?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,466
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub heffo()
   Dim LR As Long
   With Sheets("Core Data")
      LR = .Range("B" & Rows.Count).End(xlUp).Row
      With .Range("R" & Rows.Count).End(xlUp)
         .Resize(LR - .Row + 1, 5).FillDown
         .Resize(LR - .Row + 1, 5).Value = .Resize(LR - .Row + 1, 5).Value
      End With
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,555
Messages
5,548,727
Members
410,867
Latest member
Dhanas
Top