Trying to create a Macro to filter a set of data for streamlined work (See descpt)

[BigBill7]

Hello My Name is Billy I am currently working on a Macro for my company. I am relatively new to Macros and only really understand the basics. I am trying to make a simple Macro to filter down a set of data and then take that set of data and put it in a separate tab which I then rename the tab.
:banghead:
I have created the Macro by recording my-self doing all the actions and got the Macro to work. However I need to create some lines of code that will be able to make a variable list of what the new tab names may be once created initially created so the Macro doesn't only look for one tab name (EXP: in my macro "Sheet1", may be created as "Sheet2", "Sheet3"ext)

I have inserted a copy of the data I am only filtering column N so I took out all other data, The Macro should work on this file I tested before uploading, but I need some extra code that I'm not sure how to create yet.

If the initial tab is not named "Master" or subsequent tabs are not named "Sheet1", "Sheet2", "Sheet3", "Sheet4" in that order I get a debug error

My expected outcome would be what this macro achieves if nothing is off which is 3 separate tabs "0 to 100", "101 to 1000", "1001 to 3000" which the info from column N in it (See capture1 and 2 for what it should look like)

Thanks to anyone that can help!

[Paul_Hossler]

not 100% bullet proof, but simplified a little

usually don't need .Select and Scroll's, but the macro recorder captures them



Option Explicit


Sub Macro2_phh()
Dim wsMaster As Worksheet
Dim rData As Range


Application.ScreenUpdating = False

'set Master - assumed to be active sheet
Set wsMaster = ActiveSheet
Set rData = wsMaster.Cells(1, 1).CurrentRegion
If wsMaster.AutoFilterMode Then wsMaster.AutoFilterMode = False

'delete old ones
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("100 and below").Delete
Worksheets("101 to 1000").Delete
Worksheets("1001 to 3000").Delete
Application.DisplayAlerts = True
On Error GoTo 0


'add new ones
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "100 and below"
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "101 to 1000"
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "1001 to 3000"


'copy over
With rData
.AutoFilter Field:=14, Criteria1:="<=100"
.SpecialCells(xlCellTypeVisible).Copy Worksheets("100 and below").Cells(1, 1)

.AutoFilter Field:=14, Criteria1:=">=101", Operator:=xlAnd, Criteria2:="<=1000"
.SpecialCells(xlCellTypeVisible).Copy Worksheets("101 to 1000").Cells(1, 1)



.AutoFilter Field:=14, Criteria1:=">=1001", Operator:=xlAnd, Criteria2:="<=3000"
.SpecialCells(xlCellTypeVisible).Copy Worksheets("1001 to 3000").Cells(1, 1)
End With


If wsMaster.AutoFilterMode Then wsMaster.AutoFilterMode = False

'format
Call FormatSheet("100 and below")
Call FormatSheet("101 to 1000")
Call FormatSheet("1001 to 3000")


wsMaster.Select

Application.ScreenUpdating = True


End Sub


Private Sub FormatSheet(s As String)
With Worksheets(s)
.Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With

.Cells(1, 1).CurrentRegion.Columns.ColumnWidth = 100
.Cells(1, 1).CurrentRegion.Columns.AutoFit
End With
End Sub

[BigBill7]

Thanks a lot seems to work with my data now really appreciate the help!

[Paul_Hossler]

Good

You can mark your thread [Solved] -- #3 in my signature