Variable equal to values in a list

N

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.
 
G

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.
 
N

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
 
G

Gary''s Student

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


nolegrad93 said:
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 said:
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.
Click to expand...
Click to expand...
 
Top