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)

"jacqui" wrote in message
...
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

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



-----Original Message-----
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)

"jacqui" wrote in message
...
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




.

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.

--
Regards,
Tom Ogilvy

"jacqui" wrote in message
...
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



-----Original Message-----
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)

"jacqui" wrote in message
...
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




.

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


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"jacqui" wrote in message
...
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



-----Original Message-----
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)

"jacqui" wrote in message
...
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




.



.

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.

--
Regards,
Tom Ogilvy

"jacqui" wrote in message
...

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


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"jacqui" wrote in message
...
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



-----Original Message-----
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)

"jacqui" wrote in message
...
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,

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)

"jacqui" wrote in message
...
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



-----Original Message-----
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)

"jacqui" wrote in message
...
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




.

"Tom Ogilvy" wrote in message
...

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.


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.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...

"Tom Ogilvy" wrote in message
...

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.


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.

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


-----Original Message-----
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.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote
in message
...

"Tom Ogilvy" wrote in message
...

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.


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.




.