On May 19, 8:29 am, "CLR" wrote:
At the top of the code module, above the first macro, put something like
this........

Public MyName As String

Then you can use the variable you called MyName (or whatever else you want
to call it) thereafter, and the value can be carried over from macro to
macro

My first macro sets the value of MyName......

Sub Addsheets()
Dim rng As Range, cell As Range
With Worksheets("VendorList")
Set rng = .Range("B2", .Range("B2").End(xlDown))
End With
For Each cell In rng
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
MyName = cell.Value
Call AutofilterExtract
Next
End Sub

The value of MyName then changes to be whatever SheetName the above macro is
presently clycling on....

Then in the AutofilterExtract macro, which is fairly long, I use lines like
these...

Sheets(MyName).Select
Selection.AutoFilter Field:=3, Criteria1:=MyName
Worksheets(MyName).Select

hth
Vaya con Dios,
Chuck, CABGx3

"GregR" wrote in message

ups.com...



On May 18, 9:58 am, CLR wrote:
Ok.........I got it unscrambled.........the variable has to be declared
as a
Global Variable rather than being declared inside a previous
macro.........then all worked fine......this note just to close the
loop.

Vaya con Dios,
Chuck, CABGx3

"CLR" wrote:
Hi All......
I have a nifty macro that I got from this group that works just fine
to
create new sheets with the names taken from a list.

Sub Addsheets()
Dim rng As Range, cell As Range
With Worksheets("VendorList")
Set rng = .Range("B2", .Range("B2").End(xlDown))
End With
For Each cell In rng
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
Next
End Sub

I have another macro that runs the Autofilter and places the filtered
results on a specified sheet. Problem is, this takes an individual
macro for
each sheet. What I would like to do is combine the actions of both
macros
so I could 1-create new sheets named according to my list, and then
2-Run the
Autofilter macro on the DatabaseSheet to extract the relative data to
each
sheet.

I have the following lines in my data extration macro...
Selection.AutoFilter Field:=3, Criteria1:=2740
Copy Destination:=Worksheets("2740").Range("A11")

What I need is the guidance to be able to replace the 2740 in each of
those
lines with the name of each new sheet, or with the same names from the
same
list the new sheets were created from.

I hope that's clear, I know it's giving me a headache......

Any help would be much appreciated.....
Vaya con Dios,
Chuck, CABGx3- Hide quoted text -

- Show quoted text -

Chuck, what did your final macro look like. I have a similar problem

Greg- Hide quoted text -

- Show quoted text -

Chuck, thanks

Greg