View Single Post
  #1   Report Post  
neon767 neon767 is offline
Junior Member
 
Posts: 2
Default Run time error Method 'Range' of Object '_Worksheet' Failed

I have the following code:

Sub Test()

Const CL As String = "A"
Dim wsCount, outRow, bottom As Integer
wsCount = Worksheets.Count - 1
outRow = 13
Dim wsSrc(1 To 14) As Worksheet
Dim wsDest As Worksheet: Set wsDest = Sheets("Search")
wsDest.Range("A13:AV1000").ClearContents
For i = 1 To wsCount
Set wsSrc(i) = Sheets(i)
Dim LR As Long: LR = wsSrc(i).Range(CL & Rows.Count).End(xlUp).Row
With wsSrc(i).Range("A1:AV1048" & LR)
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsDest.Range("C1:C2"), CopyToRange:=wsDest.Range("A" & outRow), Unique:=True
End With
bottom = Cells(Rows.Count, "H").End(xlUp).Row
If bottom <= 13 Then
outRow = 13
wsDest.Range("A13:AV1000").ClearContents
Else
GoTo loopBreak
End If
Next i
loopBreak:

End Sub

or some variation of that. Basically, it looks through all the other worksheets and copies any data with a matching id # and all the columns in that row. However, I get the above error when
With wsSrc(i).Range("A1:AV1048" & LR)
goes to AV1049 or higher (A1:AV1048 is the highest range it will work with). Every one of the other sheets has information in rows that significantly exceed row 1048, though. I'm not sure what specifically about that number could be causing a problem, or what exactly this error means about my function.