AutoFilter using cell data in another sheet ; Subscript out of Range.

[Turbo68]

I am close from my research to get my auto filter working but its just not right. I have both sheets open at the same time and trying to sort my criteria from cell A2 from another sheet. Here is my code:

Windows("Credit Card Summary V2 JUL-AUG.xlsm").Activate
Sheets("Transactions").Activate
* ActiveSheet.Range("$A$1:$J$1000").AutoFilter Field:=3, Criteria1:=Sheets("Weekly Summary Form V4").Range("A2").Value THIS IS WHERE IT FAILS
Columns("B:J").Select
Selection.Copy
Windows("Weekly Summary Form V4").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Windows("Credit Card Summary V2 JUL-AUG.xlsm").Activate
ActiveSheet.ShowAllData
Windows("Weekly Summary Form V4").Activate

Application.ScreenUpdating = True
MsgBox "DATA SET FOR SENDING EMIAL"


When i set the criteria just as text this works just fine. Any help is greatly appreciated.

Mike

 

[JoeMo]

Possibly, the sheet "Weekly Summary Form V4" can't be found. Check to see that the spelling in your code and on the sheet's tab are identical and there are not leading or trailing spaces on the tab.

 

[Turbo68]

The workbook is named correctly, "Weekly Summary Form V4" , but I have renamed the only tab in that workbook to "Charge Acct Form". Could that be the issue?

 

[JoeMo]

The workbook is named correctly, "Weekly Summary Form V4" , but I have renamed the only tab in that workbook to "Charge Acct Form". Could that be the issue?

You said that "Weekly Summary Form V4" was a sheet name (or at least you said - "trying to sort my criteria from cell A2 from another sheet"). If it's a different workbook altogether than that's a different story. Do you want your criteria1 to come from a sheet in the workbook that contains the code or from the other workbook? If the latter, from what sheet?

EDIT: If your criteria1 comes from another (open) workbook, just record a macro of the autofilter process so you get the correct syntax for referring to that workbook & workksheet.

 

[Turbo68]

I did use the wrong wording. I am using 2 workbooks and my criteria will come from another workbook. I tried the recording of a macro to do that but when using the filters i don't know how to tell it to go to the other workbook and use that cell.

 

[Turbo68]

I actually would like this script repeat thru a list of names until it hits the end of that list. Using that list in the criteria filters for each loop.

Here is what i have got so far: ( I changed a little using the create macro method)



Windows("Credit Card Summary V2 JUL-AUG.xlsm").Activate
Sheets("Transactions").Select
ActiveWorkbook.Worksheets("Transactions").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Transactions").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A1000"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Transactions").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$1:$J$1000").AutoFilter Field:=3, Criteria1:= ** RANGE LIST THAT GOES DOWN A NAME 1 at a time**
Columns("B:J").Select
Selection.Copy
Windows("Weekly Summary Form V4").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A4").Select

MsgBox "DATA SET FOR SENDING EMIAL"

REPEAT FOR NEXT NAME IN LINE BACK TO THE TOP

My list is in the Weekly Summary Form V4 at AD4:AD20

If that helps.