Naming a Range

  • Thread starter Thread starter jacqui
  • Start date Start date
J

jacqui

Can anyone kindly help with the following?

My source data is held on a worksheet called "data". The
range is named (using CurrentRegion) "DatArea". My macro
makes copies of the data worksheet so that I can
manipulate the copied sheets in various ways thus leaving
the original sheet intact. I use various Private Subs to
delete columns and resort columns. Each copied worksheet
is named using a title from a table, such as RI, UKGAAP,
FGAAP. The number of copies is controlled by a Do Until
Loop so the macro knows when to stop.
My question is ...when VBA copies the sheet, I'd like to
name the range according to the name of the worksheet. The
range name will therefore vary on each loop. I really
should be able to get my head around this but for some
reason I'm having a complete mental block today, can
anyone help me out, urgently.

Thanks very much
Jacqui
 
Jacqui,

I'm confused. Where is the sheet name gotten from, and which range and
where?

You can add a sheet and name it with

Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = myVar

You can add a name with

myRange.Name = myRangeName

Show us the code so we can apply it.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob
My code is as follows, VBA doesn't seem to like the line
where I'm using Selection.CurrentRegion.Name etc. FYI
either the title in cell A1 or the worksheet name could be
used to name the range as these are both the same. For
example the worksheet is called RI and the text appearing
in cell A1 is RI.
Hope this makes sense.
Thanks
Jacqui

Private Sub Name_Range(ws As Worksheet)

With ws
.Range("a3").Select
Selection.EntireRow.Delete
ws.Range("a1").Select
Selection.CurrentRegion.Name = Range("A1").Value
'I also tried the following line as well
'Selection.CurrentRegion.Name = ws.Name
End With

End Sub
 
Is it RI or R1. R1 is not a legal name because it is too close to a cell
reference. I didn't have any problem naming a range RI.
 
It's RI as in letter I not number 1. That was just an
example though. I don't want to explicitly name my range
RI as in Selection.CurrentRegion.Name = "RI" cause I can
already do that.

Did you see the code in my last message 'cause that
demonstrates what I'm trying to do?

Jacqui
 
I did see it and it worked fine for me if I had RI active and I put the code
in a general module:

Sub Name_Range(ws As Worksheet)

With ws
.Range("a3").Select
Selection.EntireRow.Delete
ws.Range("a1").Select
Selection.CurrentRegion.Name = Range("A1").Value
'I also tried the following line as well
'Selection.CurrentRegion.Name = ws.Name
End With

End Sub

Sub Tester1()
Name_Range Worksheets("RI")
End Sub

If you were on another sheet or the code was in a sheet module, then the
unqualified

Range("A1").Value

would not refer to the RI sheet and could be problematic - however, I would
not expect selection.currentregion.name to have a similar problem.
 
Jacqui,

Aside from tidying the code up a bit, I couldn't find a problem. I cannot
force an error on that line. The only thing I can think is that RI is not
the activesheet when you run this, so try this modified version

Public Sub Name_Range(ws As Worksheet)

With ws
.Range("A3").EntireRow.Delete
.Range("A1").CurrentRegion.Name = .Range("A1").Value
End With

End Sub

or

Public Sub Name_Range(ws As Worksheet)

With ws
.Range("A3").EntireRow.Delete
.Range("A1").CurrentRegion.Name = .Name
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Tom Ogilvy said:
If you were on another sheet or the code was in a sheet module, then the
unqualified

Range("A1").Value

would not refer to the RI sheet and could be problematic - however, I would
not expect selection.currentregion.name to have a similar problem.
Click to expand...

I think that is the problem as Jacqui is trying to select the current in ws
(RI), which if not active will throw an error.
 
No, he said the error occurred on

Selection.CurrentRegion.Name = Range("A1").Value

The selection (and any error at that point) would already have occurred.

I also noted in my response that the sheet must be active for the code to
work as written.

So I don't think that is the current problem although certainly a
consideration of merit and a potential problem.
 
Tom/Bob

I've sussed the problem. One of my sheet names contained
a space, ie UK GAAP. When I removed this the range naming
worked as expected. Sorry for taking up your time, mind
you I was having a complete mental block yesterday.

Thanks again
Jacqui
 
Back
Top