Named ranges & protect Print_area? & apply changes across WS's?

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I have 13 WS with same layout, is there anyway I can use grouping to select
all 13 WS, then select a cell range and apply a named range to these cells
across every WS?

Basically while im setting up this WB any changes I make to one of these 13
WS needs to be applied to the others, the only differences on each WS are
contained on the first few rows of each page (4 pages per WS) (13 WS = (2 WS
per day Mon-Sat) + (1 for Sunday))

example:

each WS is setup as A1:AA236, then split in to 4 pages,
Page1=A1:AA59 Page2=A60:AA118
Page3=A119:AA177 Page4=A178:AA236

these pages are the named ranges I wish to setup on each WS. I would like to
protect the layout and formatting so thought the best way would be to assign
named ranges ?

There are also other areas that contain LOOKUP & SUM functions, and thought
named ranges for these would make doing other things easier later?

All advice much appreciated!
Steve
 
Here is a basic macro to add/change range names :-

'-------------------------------------
Sub test()
Dim RangeName As String
Dim MyRange As String
'-----------------------
RangeName = "TEST"
MyRange = "$A$1:$B$100"
'-----------------------
For Each ws In Worksheets
rg = "=" & ws.Name & "!" & MyRange
ws.Names.Add Name:=RangeName, RefersTo:=rg
ws.Activate
ws.Range(RangeName).Select
Next
End Sub
'--------------------------------------
 
could you give an example using my info please? if you need more info from
me let me know, I tried but nothing seemed to happen.

Thanks,
Steve
 
Don't know if this can help you *now*, but if you haven't *yet* created your
other sheets, you can assign a worksheet specific name to the first sheet
that you set up, and then, if and when you *copy* this sheet and rename it,
the new sheet will have this named range assigned to it as a work*sheet*
specific name also.

For example, set up Sheet1 the way you like it.
Then, select a range, and click in the name box.
Type this in the name box:
Sheet1!my_range_1

This is now a work*sheet* specific named range, where it will not be visible
in the name boxes of the other sheets.

NOW, copy this sheet, and rename it as you wish, and you will see that this
new sheet *also* contains "my_range_1", specific *only to itself*.

So, as you copy these sheets that had work*sheet* specific names originally,
they will be retained as work*sheet* specific to each and every sheet
they're copied to.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

could you give an example using my info please? if you need more info from
me let me know, I tried but nothing seemed to happen.

Thanks,
Steve
 
Back
Top