Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing variable to Access | Excel Programming | |||
Passing variable to VarType of other Variable | Excel Programming | |||
Passing variable from one sub to another | Excel Programming | |||
Passing a value to a variable from Userform | Excel Programming | |||
Question: Macro overloading, passing variable number of arguments | Excel Programming |