VBA to filter data and past into new worksheet

ngan202

New Member
Joined
Jun 13, 2016
Messages
1
Hi there, I have been struggling to build a macro that can do exactly this:

(http://www.mrexcel.com/forum/excel-...ications-autofilter-copy-paste-new-sheet.html)

For each new unique criteria, it creates a new worksheet with the title of that unique item. along with return all the columns A:I

Here is my recording of the first filter. Can someone please edit the macro so that it creates a new worksheet with the name of the unique item and pastes the rest of the columns. I tried to use the macro previously posted but can not get it to work.

Sub Macro4()
'
' Macro4 Macro
'


'
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$31").AutoFilter Field:=2, Criteria1:= _
"Administrative Services" (Want this to be dynamic and creat a new worksheet for each unique item)
Range("A1:I6").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Administrative Services"
Range("A1").Select
ActiveSheet.Paste
End Sub

Thanks in advance!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

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