Macro to repeat for all worksheets

[ccordner]

Good Afternoon.

Having just developed a Rostering package in Excel, I forgot to name ranges as I was going along.

I want to name certain ranges on each sheet with the same names (station names) so that to go to a certain station, a macro will simply pick a range and go to it.

In other words, there will be a combo box which you select (for example new pudsey) and whichever sheet you are on, it will take you to a certain range, named 'npd'.

I have written the following vba code


Sub IdentifyRanges()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
'Name ranges
Range("a1162:n1216").Name = "MISC"
Range("a366:n385").Name = "NPD"

And so on for about thirty entries

Range("a1092:n1111").Name = "WNYTL"
Range("a945:n965").Name = "WRK"
Next
End Sub

Yet it stops abruptly, part way through with a simple message box saying '400'. Any ideas why? (Excel 2007)

Thanks
Chris

 

[Andrew Poulsom]

Better show us the line on which it stops.

By the way, your code is creating workbook level names so the names will only refer to the ranges on the last worksheet.

 

[ccordner]

Good Afternoon.

It doesn't actually highlight a line. That's what confused me. I've tried running it direct from visual basic and whilst in excel - it gives me no clues!

Some of the ranges have been named however, so I wondered if there was a limit to the number of ranges you could name?

Regarding the level of the names, the roster contains several tabs, each of which refer to a different week (of the same roster). Therefore each station is duplicated on each tab.

What I am aiming to do is to navigate to that station on whichever tab is currently selected (if that makes sense), so you navigate to the week using the sheet tabs and then to the station on that sheet using the combo box and the named ranges.

Thanks
Chris.

 

[Andrew Poulsom]

Please post all your code.

 

[ccordner]

Sub IdentifyRanges()

Certainly. This is all the code in the macro. I'm sure it's something stupid I've done, but that's it.

Thanks
Chris

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Select

'Name ranges

Range("a1162:n1216").Name = "MISC"
Range("a1115:n1159").Name = "ADMIN"
Range("a832:n867").Name = "BNY"
Range("a735:n755").Name = "BEV"
Range("a389:n408").Name = "BIY"
Range("a217:n246").Name = "BDQ"
Range("a37:n81").Name = "BDIGTL"
Range("a4:n33").Name = "BDISUP"
Range("a84:n145").Name = "BDIRLF"
Range("a148:n214").Name = "BDITC"
Range("a694:n713").Name = "BDT"
Range("a344:n363").Name = "CVRLF"
Range("a717:n731").Name = "DRF"
Range("a672:n691").Name = "EYRLF"
Range("a759:n773").Name = "GOO"
Range("a457:481").Name = "GSY"
Range("a250:n295").Name = "HFX"
Range("a298:n317").Name = "HBD"
Range("a485:n514").Name = "ILK"
Range("a517:n551").Name = "KEI"
Range("a1053:n1089").Name = "MHSANN"
Range("a1020:n1049").Name = "MHSTC"
Range("a429:n453").Name = "MNN"
Range("a922:n941").Name = "MEX"
Range("a366:n385").Name = "NPD"
Range("a412:n426").Name = "RLFNPD"
Range("a871:n901").Name = "RMC"
Range("a645:n669").Name = "SET"
Range("a987:n1017").Name = "SHF"
Range("a555:n594").Name = "SHY"
Range("a597:n641").Name = "SKI"
Range("a776:n829").Name = "SYRLF"
Range("a905:n919").Name = "SWN"
Range("a969:n983").Name = "TNN"
Range("a321:n340").Name = "TOD"
Range("a1092:n1111").Name = "WNYTL"
Range("a945:n965").Name = "WRK"

Next

End Sub

 

[Andrew Poulsom]

For me it stopped on this line:

Range("a457:481").Name = "GSY"

because it's missing a letter after the colon.

As I said before that code will only create workbook level names not worksheet level names. If you want the latter you will need to precede the name withe the name of the sheet. Example:

Sub IdentifyRanges()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Select
'       Name ranges
        Range("a1162:n1216").Name = "'" & ws.Name & "'!MISC"
    Next
End Sub