naming ranges in Excel2007 using VBA

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

On Monday, May 14, 2012 7:39:51 AM UTC-5, c1802362 wrote:
> 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

works for me .... Thanks!



On Monday, May 14, 2012 9:27:20 AM UTC-4, Don Guillett wrote:
> On Monday, May 14, 2012 7:39:51 AM UTC-5, c1802362 wrote:
> > 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