How to "cross macros"

[Guest]

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

 

[Guest]

To refer to another macro within your code try this

Call OtherMacro(argument1, arg 2, arg 3 if they exist)

 

[Guest]

Thanks Barb, but I have no trouble "calling" the second macro.....where my
problem lies is in declaring a variable in the first macro and then giving it
a value, and then being able to use it in the second..........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

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

 

[GregR]

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











- Show quoted text -

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

Greg

 

[CLR]

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]

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








- Show quoted text -

Chuck, thanks

Greg