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.

--
Ian
--

[Tom Ogilvy]

Intra_mA.Copy Range("Destination")

--
Regards,
Tom Ogilvy

"Ian" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
>
>

[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" <[email protected]> wrote in message
news:%23rS34H0%[email protected]...
> Intra_mA.Copy Range("Destination")
>
> --
> Regards,
> Tom Ogilvy
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> --
>>
>>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23rS34H0%[email protected]...
>> Intra_mA.Copy Range("Destination")
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Ian" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>> --
>>>
>>>
>>
>>
>
>

[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
"Ian" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| news:[email protected]...
| > 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" <[email protected]> wrote in message
| > news:%23rS34H0%[email protected]...
| >> Intra_mA.Copy Range("Destination")
| >>
| >> --
| >> Regards,
| >> Tom Ogilvy
| >>
| >> "Ian" <[email protected]> wrote in message
| >> news:[email protected]...
| >>> 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.

--
Regards,
Tom Ogilvy



"Ian" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%23rS34H0%[email protected]...
> >> Intra_mA.Copy Range("Destination")
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "Ian" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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
> >>> --
> >>>
> >>>
> >>
> >>
> >
> >
>
>

[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" <[email protected]> wrote in message
news:u$xRVB1%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | news:[email protected]...
> | > 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" <[email protected]> wrote in message
> | > news:%23rS34H0%[email protected]...
> | >> Intra_mA.Copy Range("Destination")
> | >>
> | >> --
> | >> Regards,
> | >> Tom Ogilvy
> | >>
> | >> "Ian" <[email protected]> wrote in message
> | >> news:[email protected]...
> | >>> 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
> | >>> --
> | >>>
> | >>>
> | >>
> | >>
> | >
> | >
> |
> |
>
>

[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" <[email protected]> wrote in message
news:uSHIsL1%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:%23rS34H0%[email protected]...
>> >> Intra_mA.Copy Range("Destination")
>> >>
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>> >>
>> >> "Ian" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> 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]

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")




--
Regards,
Tom Ogilvy



"Ian" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:uSHIsL1%[email protected]...
> >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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:%23rS34H0%[email protected]...
> >> >> Intra_mA.Copy Range("Destination")
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Tom Ogilvy
> >> >>
> >> >> "Ian" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >>> 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
> >> >>> --
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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

--
Ian
--
"Tom Ogilvy" <[email protected]> wrote in message
news:u6x0HT3%[email protected]...
> 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")
>
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:uSHIsL1%[email protected]...
>> >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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> > news:%23rS34H0%[email protected]...
>> >> >> Intra_mA.Copy Range("Destination")
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Tom Ogilvy
>> >> >>
>> >> >> "Ian" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >>> 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
>> >> >>> --
>> >> >>>
>> >> >>>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>