PDA

View Full Version : Solved: add differenet worksheet names to current macro



Pete
07-31-2008, 08:40 AM
Hi

how would i make this macro run for the following worksheet "Pricing Supply" "Pricing Demand" and Allocation (Vol)...

and once the macro has run return back to cell row199 column F

Sub Name_Ranges() Range(Selection, Selection.End(xlToRight)).Select Range("E6:EC65536").Select Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:= _ FalseEnd Sub

mdmackillop
07-31-2008, 09:16 AM
Untested, but try

Option Explicit

Sub Name_Ranges()
Dim arr, a
arr = Array("Pricing Supply", "Pricing Demand", "Allocation (Vol)")
For Each a In arr
Worksheets(a).Range("E6:EC65536").CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False
Next
End Sub

CreganTur
07-31-2008, 09:18 AM
It depends on when you want the sub to run: run on worksheet open, run on button click, run whenever a change is made on a worksheet, etc. When do you want it to run?

Also, when you run code please wrap it in VBA tags- it makes it a lot easier to read, and with format the code as per VBIDE. Thanks :thumb

Bob Phillips
07-31-2008, 09:19 AM
Sub Name_Ranges()
With Worksheets("Pricing Supply")
.Range("E6:EC65536").Name = .Range("E6").Value
End With
With Worksheets("Pricing Demand")
.Range("E6:EC65536").Name = .Range("E6").Value
End With
With Worksheets("Allocation (Vol)...")
.Range("E6:EC65536").Name = .Range("E6").Value
End With
End Sub