Vbs autofilter copy and paste subroutine

DenniBrink

New Member
Joined
Jul 31, 2016
Messages
46
:) 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
Name
Sat
Sun
Mon
Tue
Wed
Thu
Fri
Bob
NS
NS
Clean
Inspect
Build
Inspect
Clean
Richard
Clean
NS
NS
Clean
Inspect
Build
Inspect

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
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"
 
Last edited:

DenniBrink

New Member
Joined
Jul 31, 2016
Messages
46
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

New Member
Joined
Jul 31, 2016
Messages
46
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top