Subscript out of range VBA

[tHE fLY]

Hi All,

I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet. any help would be appreciated.

Sub Dater()
Static Count As Integer
Dim Wb As Workbook
Dim Ws As Worksheet
Dim FileStr As String
Set Wb = Workbooks("Pending Approval by Responsible User")
Set Ws = Worksheets("Pending Approval by Responsible") <----- getting error here.. Only get error hen am using for control to call Macro
With Ws.Range("A3").CurrentRegion
Range("A3").Select
Selection.AutoFilter
.AutoFilter Field:=7, Criteria1:=">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 10)
End With
ActiveWorkbook.Worksheets("Pending Approval by Responsible").AutoFilter.Sort. _
SortFields.Add Key:=Range("G3:G1000"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Pending Approval by Responsible").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[VoG]

Maybe

Set Ws = Wb.Worksheets("Pending Approval by Responsible")

 

[NeonRedSharpie]

Set Ws = ActiveWorkbook.Sheets("Pending Approval by Responsible")

or

Set Ws = Wb.Sheets("Pending Approval by Responsible")

depending on what you're trying to accomplish.

 

[CodeNinja]

the fly,
You are getting this error, because the workbook the macro is running from cannot find the worksheet "Pending Approval by Responsible". I am assuming your worksheet "Pending Approval by Responsible" is located in the workbook "Pending Approval by Responsible User". If this is the case, try Set Ws = Wb.Worksheets("Pending Approval by Responsible").

This will only work if the instance of excel that the macro is running from also has an open copy of "Pending Approval by Responsible User". If that is not going to be the case, you will have to open that workbook with set wb = application.workbooks.open(path)

Hope this helps,

CN.

 

[tHE fLY]

Thanks.. all of you were right on! I am learning VBA so sorry if that seems like a softball question. =)