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