JasmineCorp Directory   
  
Usenet News Group Archives!!!

Usenet Groups:

 

 

 

 

 

Re: How to "cross macros"
--------------------------------------------------------------------------------------------
Newsgroup: microsoft.public.excel.misc
Posted by: CLR
2007-05-19 15:29:09

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
news:1179517071.583874.104250@o5g2000hsb.googlegroups.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
>


 

 

 

More >> 

Domain Registration:
.com .org .net
.info .biz .us