About Us

Gary''s Student

Check back tomorow
--
Gary''s Student - gsnu200805

"nolegrad93" wrote:

So, here's where I'm at... I'm not sure how to bridge the gap from Servient
macro to Macro1 that begins -- Range("List1").Select

Thanks for your help!

_____________________________________

Sub servient()
'uses names in list as values for variable

For Each n In ActiveWorkbook.Names
v = n.RefersTo
If InStr(v, "Sheet3") < 0 Then
Application.Run "Book1!Macro1"
End If
Next
End Sub

Sub Macro1()

'
' Macro1 Macro
' Macro recorded 9/23/2008
'

Range("List1").Select
Selection.Copy
Sheets("Sheet2").Select
If Application.WorksheetFunction.CountA("A:A") = 0 Then
[A1].Select
Else
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
If Err < 0 Then
On Error GoTo 0
[A65536].End(xlUp)(2, 1).Select
End If
On Error GoTo 0
End If
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("B14").Select
Application.CutCopyMode = False
End Sub

"Gary''s Student" wrote:

Say we only want to run a macro on Named Ranges on Sheet1:

Sub servient()
For Each n In ActiveWorkbook.Names
v = n.RefersTo
If InStr(v, "Sheet1") < 0 Then
'call your macro
End If
Next
End Sub

So we look at all the Named Ranges, but only call the macro when the range
is on Sheet1.
--
Gary''s Student - gsnu200805

"nolegrad93" wrote:

I'm trying to write a macro that will perfom the same subroutine on multiple
ranges. All ranges are on a single worksheet. Each range is named. Each
name appears in a list on a separate worksheet.

I think I need to declare a variable and somehow use the list of range names
as values for the variable.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
selecting values from a list to equal a given value	[email protected]	Excel Worksheet Functions	3	December 23rd 08 11:09 PM
Make all variable values in a Array equal to 0	RyanH	Excel Programming	8	August 1st 08 02:34 AM
counting the last 3 values not equal to x in a list	bouncebackability	Excel Worksheet Functions	3	April 9th 08 07:01 PM
Macro to select a list of values greater than or equal to a value	DOOGIE	Excel Programming	9	April 25th 07 11:04 AM
list values equal in 2 worksheets?	NEVA LOCKETT	Excel Worksheet Functions	2	February 23rd 05 03:21 PM

Menu

About Us