nidenikolev
New Member
- Joined
- Jun 6, 2018
- Messages
- 20
Working on a script and this line of code keeps coming up with an object required error : .AutoFill Destination:=.Range("AT2:AT" & lastrow)
I also have it labeled towards the bottom of the script, can anyone help out?
I also have it labeled towards the bottom of the script, can anyone help out?
Code:
Sub Update()
'File Paths
Dim Preplan As String
Dim PS_Export As String
Preplan = "M:\Template2.xlsx"
PS_Export = "M:\PS_Export.xlsx"
'Open WB's
Dim PP_WB As Workbook
Dim PS_WB As Workbook
Set PP_WB = Workbooks.Open(Filename:=Preplan, Password:="")
Set PS_WB = Workbooks.Open(Filename:=PS_Export)
Dim PP_WS As Worksheet
Set PP_WS = PP_WB.Sheets("2017 Pre-Planning Emp Detail")
Dim PS_WS As Worksheet
Set PS_WS = PS_WB.Sheets("ps")
Dim AVCell As Long
lastrow = PP_WS.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = PS_WS.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
PP_WB.Activate
With PP_WS
With .Range("AE2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$K,11,FALSE)"
.AutoFill Destination:=Range("AE2:AE" & lastrow)
End With
With .Range("AF2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$H,8,FALSE)"
.AutoFill Destination:=Range("AF2:AF" & lastrow)
End With
With .Range("AG2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AY,50,FALSE)"
.AutoFill Destination:=Range("AG2:AG" & lastrow)
End With
With .Range("AH2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$O,15,FALSE)"
.AutoFill Destination:=Range("AH2:AH" & lastrow)
End With
With .Range("AI2")
.Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$P,16,FALSE)"
.AutoFill Destination:=Range("AI2:AI" & lastrow)
End With
End With
PS_WB.Activate
With PS_WS
.Columns("AH:AH").Insert Shift:=xlToRight
With .Range("AH2")
.Formula = "=AD2+AG2"
.AutoFill Destination:=Range("AH2:AH" & lastrow2)
.Range("AH1") = "Variable Comp"
End With
End With
PP_WB.Activate
With PP_WS
With .Range("AR2")
.Formula = "=IF(F2=""X"",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AH,34,FALSE),(AS2+AU2+AX2))"
.AutoFill Destination:=Range("AR2:AR" & lastrow)
End With
With .Range("AS2")
.Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE),(AR2-AX2))"
.AutoFill Destination:=Range("AS2:AS" & lastrow)
End With
Range("AT:AV").Insert Shift:=xlToRight
With .Range("AT2").Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE))"
.AutoFill Destination:=.Range("AT2:AT" & lastrow) <-------**THIS IS THE ROW WHERE I'M GETTING AN OBJECT REQURIED ERROR**
.Range("AU2").Formula = "=IF(F2=""X"",AR2-AX2)"
.AutoFill Destination:=.Range("AU2:AU" & lastrow)
.Range("AV1") = "2017 Planned Annual IC"
End With
For AVCell = 2 To lastrow
.Range("AV" & AVCell).Value = .Range("AT" & AVCell).Value & .Range("AU" & AVCell).Value
Next AVCell
End With
Last edited by a moderator:
