Set range or named range?

[Ian]

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.

 

[Tom Ogilvy]

Intra_mA.Copy Range("Destination")

 

[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, 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?

 

[Jim Rech]

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
| 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
| --
| | > 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
| > --
| > | >> Intra_mA.Copy Range("Destination")
| >>
| >> --
| >> Regards,
| >> Tom Ogilvy
| >>
| >> | >>> 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 Ogilvy]

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.

 

[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]

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.

 

[Tom Ogilvy]

Dim Intra_mA as Range
With Worksheets("Lookup")
Set Intra_mA = .Range(.Range("A13"), _
.Range("A13").end(xldown)).Resize(,6)
End With

or if you did create a named range

Set Intra_mA = Range("MyLookupRange")

 

[Ian]

Thanks, Tom. It's all working fine. I've created the named ranges manually
and successfully copied them across with code. I took out the Dim line and
it's still working well.