I am constantly getting a Object Required Error on "LastR = " and have no idea why. It appears to be something wrong with the argument I'm using to try to define LastR but I dont understand what is going wrong please can someone help.
Sub CopyUniqueValidation()
Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
'Defines S1 as a Worksheet with the same name as the contents of Customer Trend Sheet Cell B2
Set s1 = Worksheets(Worksheets("Customer Trend").Range("B2").Value)
'Defines S2 as Working Sheet
Set s2 = Sheets("Workings")
'Defines S3 as Customer Trend Sheet
Set s3 = Sheets("Customer Trend")
Dim LastR As Long, DataRange As Range
'Finds Number of the last row cell in column B of Workings sheet
Set LastR = s2.Range("B1").CurrentRegion.Rows.Count
'Creates a "DataRange" from B1 to last row cell in Column B
Set DataRange = s2.Range("B1:B" & LR)
'Takes Data from Customer Column of defined PPA Sheet (based on Customer Trend B2) and pastes it to Working Sheet Column B
s1.Range("H6:H500").Copy s2.Range("b1")
'Removes Duplicates from Column B Working sheet
s2.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
'Sorts Column B Working Sheet Ascending
s2.Range("B:B").Sort Key1:=s2.Range("B1"), Order1:=xlAscending, Header:=xlNo
'Creates DataValidation List in Customer Trend B3 based on DataRange above
With s3.Range("B3")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DataRange
End With
End With
End Sub