Vbs autofilter copy and paste subroutine

[DenniBrink]

Hello everyone! I need another dose of Mr. Excel brilliance. I am trying to develop a subroutine that will autofilter, copy, and paste columns of data from one worksheet to another. Below is an example of a table (sht: "Master Daily Sched") I want to autofilter with the string criteria1:="NS".

B C D E F G H I
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Sat
[/TD]
[TD]Sun
[/TD]
[TD]Mon
[/TD]
[TD]Tue
[/TD]
[TD]Wed
[/TD]
[TD]Thu
[/TD]
[TD]Fri
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]NS
[/TD]
[TD]NS
[/TD]
[TD]Clean
[/TD]
[TD]Inspect
[/TD]
[TD]Build
[/TD]
[TD]Inspect
[/TD]
[TD]Clean
[/TD]
[/TR]
[TR]
[TD]Richard
[/TD]
[TD]Clean
[/TD]
[TD]NS
[/TD]
[TD]NS
[/TD]
[TD]Clean
[/TD]
[TD]Inspect
[/TD]
[TD]Build
[/TD]
[TD]Inspect
[/TD]
[/TR]
</tbody>[/TABLE]

The following is an example of VBS script I am trying to revise:

Sub SATFILTER()
' SATFILTER Macro
' Filters Master Daily Schedule Worksheet for Employees Assigned to Work Saturday.
Application.ScreenUpdating = False
Sheets("SAT_ASSIGNMENTS").Select
Range("B8:C38").Clear
Sheets("MASTER DAILY SCHED").Select "THIS AREA NEEDS REVISING; ERROR HANDLING OCCURS"
ActiveSheet.Range("$C$4:$I$56").AutoFilter Field:=1, Criteria1:=Array( _ "I believe this should be define as a string = "NS"
"3003/3009", "3070/3017", "3070/3086", "3087/3088/3089", "3090/3091", "3092/3093", _
"AFCS", "AFSM", "APPS #1", "APPS #2", "DBCS", "E. Battery Rm", "FSS", "SPSS/APBS"), _
Operator:=xlFilterValues
Range("B8:C56").Select
Selection.Copy
Sheets("SAT_ASSIGNMENTS").Select "Problem Here; Error Handling Occurs"
Range("B8").Select
ActiveSheet.Paste
Sheets("MASTER DAILY SCHED").AutoFilterMode = False
Sheets("SAT_ASSIGNMENTS").Select
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I would like for you to tell me what you want to do.
You said:
"I want to autofilter with the string criteria1:="NS"."

But then I see code about criteria = color.......

We need the range to filter or Table Name
Like Table1 or Range("A1:G")
We need the criteria and what to do with the filtered data.

And we need sheet names not as you said Quote:"from one worksheet to another"

Do not say "read my script that does not work and figure it out"

 

[DenniBrink]

Indeed the beauty is in the details. I apologize for the lack of clarity. Here are the specifics:

SheetNames: "SAT_ASSIGNMENTS" 'The destination for the copy and paste.' Table 1.
"MASTER DAILY SCHED" 'The table I want to autofilter and critera1: "NS". Table 2
Range: "MASTER DAILY SCHED (C4:I56)

 

[DenniBrink]

Sub SATFILTER()
' SATFILTER Macro
' Filters Master Daily Schedule Worksheet for Employees Assigned to Work Saturday.
Application.ScreenUpdating = False
Sheets("SAT_ASSIGNMENTS").Select 'Table 1'
Range("B8:C38").Clear
Sheets("MASTER DAILY SCHED").Range("$C$4:$I$56").AutoFilter Field:=1, Criteria1:=Array( _ 'Table 2 - Filters Column C for all cells that contain "NS" '
"3003/3009", "3070/3017", "3070/3086", "3087/3088/3089", "3090/3091", "3092/3093", _
"AFCS", "AFSM", "APPS #1", "APPS #2", "DBCS", "E. Battery Rm", "FSS", "SPSS/APBS"), _
Operator:=xlFilterValues
Range("B8:C56").Select
Selection.Copy
Sheets("SAT_ASSIGNMENTS").Select 'Table 1 Destination for the Copy & Paste'
Range("B8").Select
ActiveSheet.Paste
Sheets("MASTER DAILY SCHED").AutoFilterMode = False
Sheets("SAT_ASSIGNMENTS").Select
Application.ScreenUpdating = True
End Sub


The macro filter works. I desire to write a script that is more efficient and 100% error free.