naming ranges in Excel2007 using VBA

[c1802362]

was recently "upgraded" from Win2000/MSO2003 to Win7/MSO2007

The following code written in Excel 2003 works in both Excel 2003 and
2007, but when I add code to a module in the exact same syntax in
Excel 2007, the system refuses to recognize it and doesn't flag me
there's an error.

The code:

' Name the range
Range("A1").CurrentRegion.Select ' select the range you want
named
ActiveWorkbook.Names.Add Name:="RangeName1", RefersToR1C1:=Selection

Any ideas as to why the system is acitng this way? Anysuggestions on
how to fix the problem?

Art

 

[Don Guillett]

was recently "upgraded" from Win2000/MSO2003 to Win7/MSO2007

The following code written in Excel 2003 works in both Excel 2003 and
2007, but when I add code to a module in the exact same syntax in
Excel 2007, the system refuses to recognize it and doesn't flag me
there's an error.

The code:

' Name the range
Range("A1").CurrentRegion.Select ' select the range you want
named
ActiveWorkbook.Names.Add Name:="RangeName1", RefersToR1C1:=Selection

Any ideas as to why the system is acitng this way? Anysuggestions on
how to fix the problem?

Art

You probably wanted tt but try my simpler version

Sub ttt()
ActiveSheet.UsedRange.Select
ActiveWorkbook.Names.Add Name:="RangeName1", _
RefersToR1C1:=Selection

End Sub
Sub nameit()
ActiveSheet.UsedRange.Name = "asusedrange"
ActiveSheet.Range("a1").CurrentRegion _
..Name = "a1curreg"

End Sub

 

[c1802362]

works for me .... Thanks!

You probably wanted tt but try my simpler version

Sub ttt()
ActiveSheet.UsedRange.Select
ActiveWorkbook.Names.Add Name:="RangeName1", _
RefersToR1C1:=Selection

End Sub
Sub nameit()
ActiveSheet.UsedRange.Name = "asusedrange"
ActiveSheet.Range("a1").CurrentRegion _
.Name = "a1curreg"

End Sub