Passing a Variable to a Macro
 

I want to create a simple Macro that sorts a list by the criteria that a user
enters. I know I would hard code this macro with something liek:
ActiveSheet.Range("$A$5:$CW$355").AutoFilter Field:=77, Criteria1:="0008"

but I want the user to be prompted to select the Criteria, "0008" in this
instance, and then have the list sort.

I am hoping it is pretty easy...

Thanks

Passing a Variable to a Macro
 

Iam,

It is easy:

ActiveSheet.Range("$A$5:$CW$355").AutoFilter Field:=77,
Criteria1:=InputBox("Criteria?")

HTH,
Bernie
MS Excel MVP


"IAM" wrote in message
...
I want to create a simple Macro that sorts a list by the criteria that a
user
enters. I know I would hard code this macro with something liek:
ActiveSheet.Range("$A$5:$CW$355").AutoFilter Field:=77, Criteria1:="0008"

but I want the user to be prompted to select the Criteria, "0008" in this
instance, and then have the list sort.

I am hoping it is pretty easy...

Thanks

Passing a Variable to a Macro
 

Wow. That was super easy. Of course it opens up many more possibilities in my
mind.

I there any way to give a pick list from a named range in the excel sheet
for a variable in the criteria for this macro?

OR

Is there any way to loop this macro so it keeps feeding in variables from a
range in the document until there are no more? The rest of this macro takes
the filtered list and copies to a new sheet. This allows a master list of
sales opportunity to be broekn out and formatted for each territory...

Iam

Passing a Variable to a Macro
 

Iam,

Is there any way to loop this macro so it keeps feeding in variables from a
range in the document until there are no more? The rest of this macro takes
the filtered list and copies to a new sheet. This allows a master list of
sales opportunity to be broekn out and formatted for each territory...

Of course - you should have asked for that up front.

The code below is written on the assumption that you have no completely blank rows or columns in
your data set, set to export based on column BY....

HTH,
Bernie
MS Excel MVP


Sub ExportSheetsFromDatabase()
'Based on the value in the 77th column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myCol As Integer

'ActiveSheet.Range("$A$5:$CW$355").AutoFilter Field:=77
'Export based on values in column BY ( the 77th column of A:CW)

myCol = 77

Set myArea = ActiveSheet.Range("$A$5:$CW$" & Cells(Rows.Count, 1).End(xlUp).Row)
Set myArea = myArea(2, myCol).Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=myCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub

"IAM" wrote in message
...
Wow. That was super easy. Of course it opens up many more possibilities in my
mind.

I there any way to give a pick list from a named range in the excel sheet
for a variable in the criteria for this macro?

OR

Is there any way to loop this macro so it keeps feeding in variables from a
range in the document until there are no more? The rest of this macro takes
the filtered list and copies to a new sheet. This allows a master list of
sales opportunity to be broekn out and formatted for each territory...

Iam

Passing a Variable to a Macro
 

Hi

Create a list with all territories and name it as "MyNamedRange". This
should do it:

Sub LoopFilter()
Application.ScreenUpdating = False
Dim DataSh As Worksheet
Set DataSh = ActiveSheet ' Or refer to a specific sheet like:
Worksheets("Sheet1")
DataSh.Activate
Range("$A$5:$CW$355").Select
For Each cell In DataSh.Range("MyNamedRange").Cells
If cell.Value = "" Then Exit For ' exit when next cell in
MyNamedRange is empty
MyCriteria = cell.Value
Selection.AutoFilter Field:=1, Criteria1:=MyCriteria
Set newSh = Sheets.Add(after:=Sheets(Sheets.Count))
newSh.Name = MyCriteria
DataSh.Activate
Selection.Copy Destination:=newSh.Range("A1") ' change range to
suit
Next
Selection.AutoFilter
Application.ScreenUpdating = True
End Sub

Regards,
Per

On 9 Dec., 14:32, IAM wrote:
Wow. That was super easy. Of course it opens up many more possibilities in my
mind.

I there any way to give a pick list from a named range in the excel sheet
for a variable in the criteria for this macro?

OR

Is there any way to loop this macro so it keeps feeding in variables from a
range in the document until there are no more? The rest of this macro takes
the filtered list and copies to a new sheet. This allows a master list of
sales opportunity to be broekn out and formatted for each territory...

Iam