I am creating a series of sheets where basic data is entered on a userform
then ranges are copied from a lookup sheet are copied to a sheet to create a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and how would
I then implement the copy routine?

I am thinking this because I just came across an error in my original setup
for one of the sheets which meant I had to insert another line into a range,
thereby offsetting all the ranges below it.

--
Ian
--

Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I am creating a series of sheets where basic data is entered on a userform
then ranges are copied from a lookup sheet are copied to a sheet to create
a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and how
would
I then implement the copy routine?

I am thinking this because I just came across an error in my original
setup
for one of the sheets which meant I had to insert another line into a
range,
thereby offsetting all the ranges below it.

--
Ian
--

So basically I'm doing away with my Dim & Set lines and leaving the Copy
line the same. Easier to implement than I thought.

Thanks, Tom.

--
Ian
--
"Tom Ogilvy" wrote in message
...
Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I am creating a series of sheets where basic data is entered on a
userform
then ranges are copied from a lookup sheet are copied to a sheet to
create
a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and how
would
I then implement the copy routine?

I am thinking this because I just came across an error in my original
setup
for one of the sheets which meant I had to insert another line into a
range,
thereby offsetting all the ranges below it.

--
Ian
--

Tom, I'm having difficulty with this. If I do away with the Dim & Set lines
and leave the Copy line the same, I get a Run-time error '424': Object
required. Am I missing something glaringly obvious?

--
Ian
--
"Ian" wrote in message
...
So basically I'm doing away with my Dim & Set lines and leaving the Copy
line the same. Easier to implement than I thought.

Thanks, Tom.

--
Ian
--
"Tom Ogilvy" wrote in message
...
Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I am creating a series of sheets where basic data is entered on a
userform
then ranges are copied from a lookup sheet are copied to a sheet to
create
a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and how
would
I then implement the copy routine?

I am thinking this because I just came across an error in my original
setup
for one of the sheets which meant I had to insert another line into a
range,
thereby offsetting all the ranges below it.

--
Ian
--

Range("SrcRg").Copy Range("DestRg")

should work. I don't usually do a Set of a range variable unless I'm going
to use it several times.

--
Jim
"Ian" wrote in message
...
| Tom, I'm having difficulty with this. If I do away with the Dim & Set
lines
| and leave the Copy line the same, I get a Run-time error '424': Object
| required. Am I missing something glaringly obvious?
|
| --
| Ian
| --
| "Ian" wrote in message
| ...
| So basically I'm doing away with my Dim & Set lines and leaving the Copy
| line the same. Easier to implement than I thought.
|
| Thanks, Tom.
|
| --
| Ian
| --
| "Tom Ogilvy" wrote in message
| ...
| Intra_mA.Copy Range("Destination")
|
| --
| Regards,
| Tom Ogilvy
|
| "Ian" wrote in message
| ...
| I am creating a series of sheets where basic data is entered on a
| userform
| then ranges are copied from a lookup sheet are copied to a sheet to
| create
| a
| configured layout.
|
| To create the ranges I am currently using:
| Dim Intra_mA as Range
| Set Intra_mA = Worksheets("Lookup").Range("A13:F20")
|
| To copy the range I then use
|
| Intra_mA.Copy Worksheets("Dental").Range("A16")
|
| Would it be easier to create named ranges on the lookup sheet, and how
| would
| I then implement the copy routine?
|
| I am thinking this because I just came across an error in my original
| setup
| for one of the sheets which meant I had to insert another line into a
| range,
| thereby offsetting all the ranges below it.
|
| --
| Ian
| --
|
|
|
|
|
|
|
|

Soem of the ranges are being used several times (up to 6) and by defining
the ranges, I keep the "working" part of the code more legible.

--
Ian
--
"Jim Rech" wrote in message
...
Range("SrcRg").Copy Range("DestRg")

should work. I don't usually do a Set of a range variable unless I'm
going
to use it several times.

--
Jim
"Ian" wrote in message
...
| Tom, I'm having difficulty with this. If I do away with the Dim & Set
lines
| and leave the Copy line the same, I get a Run-time error '424': Object
| required. Am I missing something glaringly obvious?
|
| --
| Ian
| --
| "Ian" wrote in message
| ...
| So basically I'm doing away with my Dim & Set lines and leaving the
Copy
| line the same. Easier to implement than I thought.
|
| Thanks, Tom.
|
| --
| Ian
| --
| "Tom Ogilvy" wrote in message
| ...
| Intra_mA.Copy Range("Destination")
|
| --
| Regards,
| Tom Ogilvy
|
| "Ian" wrote in message
| ...
| I am creating a series of sheets where basic data is entered on a
| userform
| then ranges are copied from a lookup sheet are copied to a sheet to
| create
| a
| configured layout.
|
| To create the ranges I am currently using:
| Dim Intra_mA as Range
| Set Intra_mA = Worksheets("Lookup").Range("A13:F20")
|
| To copy the range I then use
|
| Intra_mA.Copy Worksheets("Dental").Range("A16")
|
| Would it be easier to create named ranges on the lookup sheet, and
how
| would
| I then implement the copy routine?
|
| I am thinking this because I just came across an error in my
original
| setup
| for one of the sheets which meant I had to insert another line into
a
| range,
| thereby offsetting all the ranges below it.
|
| --
| Ian
| --
|
|
|
|
|
|
|
|

I wasn't rewriting your code, only showing how to copy to a named range.


Dim Intra_mA as Range
'Worksheets("Dental").Range("A16").Name = "Destination"
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")
Intra_mA.Copy worksheets("Dental").Range("Destination")


this line
Worksheets("Dental").Range("A16").Name = "Destination"

just illustrates that at some point before using it, you need to define the
named range "Destination". You can do it manually - one time - no code
needed.

--
Regards,
Tom Ogilvy



"Ian" wrote in message
...
Tom, I'm having difficulty with this. If I do away with the Dim & Set
lines
and leave the Copy line the same, I get a Run-time error '424': Object
required. Am I missing something glaringly obvious?

--
Ian
--
"Ian" wrote in message
...
So basically I'm doing away with my Dim & Set lines and leaving the Copy
line the same. Easier to implement than I thought.

Thanks, Tom.

--
Ian
--
"Tom Ogilvy" wrote in message
...
Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I am creating a series of sheets where basic data is entered on a
userform
then ranges are copied from a lookup sheet are copied to a sheet to
create
a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and how
would
I then implement the copy routine?

I am thinking this because I just came across an error in my original
setup
for one of the sheets which meant I had to insert another line into a
range,
thereby offsetting all the ranges below it.

--
Ian
--

I think we're talking at crossed purposes here. I was wanting to make the
SOURCE ranges named, such that if I had to add something to the range, it
wouldn't mean me having to rewrite a lot of code to go with it.

In other words (using the example I gave), Intra_mA is currently a range on
Lookup as defined in the Set line, but I wanted it to be a named range so
that if for example I needed to add an extra row, I could do that on Lookup
and redefine the named range, rather than having to go into the code.

More importantly it would mean that the ranges below Intra_mA in Lookup
would automatically update.

--
Ian
--
"Tom Ogilvy" wrote in message
...
I wasn't rewriting your code, only showing how to copy to a named range.


Dim Intra_mA as Range
'Worksheets("Dental").Range("A16").Name = "Destination"
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")
Intra_mA.Copy worksheets("Dental").Range("Destination")


this line
Worksheets("Dental").Range("A16").Name = "Destination"

just illustrates that at some point before using it, you need to define
the
named range "Destination". You can do it manually - one time - no code
needed.

--
Regards,
Tom Ogilvy



"Ian" wrote in message
...
Tom, I'm having difficulty with this. If I do away with the Dim & Set
lines
and leave the Copy line the same, I get a Run-time error '424': Object
required. Am I missing something glaringly obvious?

--
Ian
--
"Ian" wrote in message
...
So basically I'm doing away with my Dim & Set lines and leaving the
Copy
line the same. Easier to implement than I thought.

Thanks, Tom.

--
Ian
--
"Tom Ogilvy" wrote in message
...
Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I am creating a series of sheets where basic data is entered on a
userform
then ranges are copied from a lookup sheet are copied to a sheet to
create
a
configured layout.

To create the ranges I am currently using:
Dim Intra_mA as Range
Set Intra_mA = Worksheets("Lookup").Range("A13:F20")

To copy the range I then use

Intra_mA.Copy Worksheets("Dental").Range("A16")

Would it be easier to create named ranges on the lookup sheet, and
how
would
I then implement the copy routine?

I am thinking this because I just came across an error in my original
setup
for one of the sheets which meant I had to insert another line into a
range,
thereby offsetting all the ranges below it.

--
Ian
--