Object Required Error

[JoshPocock]

I have the following code but I keep getting an Object Required Error (noted in the code).


Sub UpdateProspects()

'Unprotects Sheet so Macro can Run
Sheets("Top_Prospects").Unprotect Password:="positrol1"

' Copy_Prospects Macro
' Macro recorded 4/15/2011 by Josh Pocock
'
Target = "A3"
Sheets("Top_Prospects").Select
ActiveCell.FormulaR1C1 = "=Prospect_Inputs!R[-1]C[7]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=Prospect_Inputs!R[-1]C[7]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Prospect_Inputs!R[-1]C[7]"
Range("A3:C3").Select
Selection.AutoFill Destination:=Range("A3:C1000"), Type:=xlFillDefault
Range("A3:C1000").Select

Target = "A3"


'Delete Prospects and Move Cells Up

Dim intCol As Integer
For intCol = 1 To 3
Range(Cells(2, intCol), Cells(1000, intCol)). _
SpecialCells(xlCellTypeFormulas, 16).Delete Shift:=xlUp
Next intCol

'Reset Cells to default values once a Prospect has Ordered or has been Given up on
Sheets("Prospect_Inputs").Select
ActiveSheet.Range("A1:A1000").SpecialCells(xlCellTypeBlanks).Value = "Empty"

Dim lngLastRow As Long
Dim i As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lngLastRow
If ActiveSheet.Cells(i, "E").Value <> "" Then ActiveSheet.Cells(i, "A").Value = "Empty"
If ActiveSheet.Cells(i, "E").Value <> "" Then Range(Cells(i, "B"), Cells(i, "C")).Value = 0
If ActiveSheet.Cells(i, "E").Value <> "" Then ActiveSheet.Cells(i, "D").Value = 1
If ActiveSheet.Cells(i, "E").Value <> "" Then ActiveSheet.Cells(i, "E").Value = ""
Next i
'If Probability falls to "1" customer gets moved to "Old-Prospects" Sheet
If Target.Column = 6 And Target.Cells.Count = 1 Then '<-- OBJECT REQUIRED ERROR
If Target.Value = 1 Then
MyAddress = Target.Address
'Find Next Empty Cell In Old_Prospects Sheet Column A
NextRow = Old_Prospects.Range("A" & Rows.Count).End(xlUp).Row + 1
'Cut and Paste Target Row to Sheet2
Target.EntireRow.Cut Destination:=Old_Prospects.Range("A" & NextRow)
End If
End If
For i = 2 To lngLastRow
If ActiveSheet.Cells(i, "A").Value = "Empty" Then ActiveSheet.Cells(i, "D").Value = 1
If ActiveSheet.Cells(i, "A").Value = "Empty" Then ActiveSheet.Cells(i, "A").Value = ""
Next i
Sheets("Top_Prospects").Select

'Reprotect Sheet After Macro Runs
Sheets("Top_Prospects").Protect Password:="positrol1"
End Sub

 

[Jonmo1]

Change

Target = "A3"

to

Set Target = Range("A3")

 

[JoshPocock]

I knew it was something simple that I was missing. Thanks so much!