Re: How to "cross macros"
Posted by: CLR
At the top of the code module, above the first macro, put something like
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
My first macro sets the value of MyName......
Dim rng As Range, cell As Range
Set rng = .Range("B2", .Range("B2").End(xlDown))
For Each cell In rng
ActiveSheet.Name = cell.Value
MyName = cell.Value
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
Selection.AutoFilter Field:=3, Criteria1:=MyName
Vaya con Dios,
"GregR" wrote in message
> On May 18, 9:58 am, CLR wrote:
> > Ok.........I got it unscrambled.........the variable has to be declared
> > Global Variable rather than being declared inside a previous
> > macro.........then all worked fine......this note just to close the
> > Vaya con Dios,
> > Chuck, CABGx3
> > "CLR" wrote:
> > > Hi All......
> > > I have a nifty macro that I got from this group that works just fine
> > > 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
> > > each sheet. What I would like to do is combine the actions of both
> > > so I could 1-create new sheets named according to my list, and then
> > > Autofilter macro on the DatabaseSheet to extract the relative data to
> > > 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
> > > lines with the name of each new sheet, or with the same names from the
> > > 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