Variable equal to values in a list

[nolegrad93]

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.

 

[Gary''s Student]

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.

 

[nolegrad93]

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]

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

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

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.