All...
I thank all in advance -

Attached is the code that I've built to identify a range and Define a name
for use in a workbook... As I step through the code it properely outlines
the range as is defined, however when I view the range in Excel it is not as
I expected...
The first named range always works - NewCoverageCodes, however after that
each named range is incorrect
CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
expected it to be 'Coverage Codes'!$A$2:$A$7 and
CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when I
expected it to be 'Coverage Codes'!$B$2:$B$7 and
CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
expected it to be 'Coverage Codes'!$C$2:$C$7 and

Sub AddCoverageCodes()
Dim NewCoverageCodes As Range
Dim CoverageCodes As Range
Dim CoverageMonths As Range
Dim CoverageMiles As Range
Dim CoverageEffectiveDate As Range
Dim i As Integer
Dim LoopCount As Long
Dim NewCoverageCodeCount As Long

Sheets("Coverage Codes").Select
Range("A1").Select
NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)

Selection.CurrentRegion.Select
Set NewCoverageCodes = (Selection.CurrentRegion)
ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
RefersTo:=NewCoverageCodes

Range("A2:A" & NewCoverageCodeCount).Select
Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageCodes", RefersTo:=CoverageCodes

Range("B2:B" & NewCoverageCodeCount).Select
Set CoverageMonths = (Selection.Range("B2:B" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMonths", RefersTo:=CoverageMonths

Range("C2:C" & NewCoverageCodeCount).Select
Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMiles", RefersTo:=CoverageMiles

Range("D2" & NewCoverageCodeCount).Select
Set CoverageEffectiveDate = (Selection.Range("C2:C" &
NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
RefersTo:=CoverageEffectiveDate



--

Joe Mac

You are probably incrementing your "new coverage code count" within the loop

sub makenames()
Sheets("Coverage Codes").Select
NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count

Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
'etc for c & d

end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joe Mac" <(E-Mail Removed)> wrote in message
news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
> All...
> I thank all in advance -
>
> Attached is the code that I've built to identify a range and Define a name
> for use in a workbook... As I step through the code it properely outlines
> the range as is defined, however when I view the range in Excel it is not
> as
> I expected...
> The first named range always works - NewCoverageCodes, however after that
> each named range is incorrect
> CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
> expected it to be 'Coverage Codes'!$A$2:$A$7 and
> CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when I
> expected it to be 'Coverage Codes'!$B$2:$B$7 and
> CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
> expected it to be 'Coverage Codes'!$C$2:$C$7 and
>
> Sub AddCoverageCodes()
> Dim NewCoverageCodes As Range
> Dim CoverageCodes As Range
> Dim CoverageMonths As Range
> Dim CoverageMiles As Range
> Dim CoverageEffectiveDate As Range
> Dim i As Integer
> Dim LoopCount As Long
> Dim NewCoverageCodeCount As Long
>
> Sheets("Coverage Codes").Select
> Range("A1").Select
> NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)
>
> Selection.CurrentRegion.Select
> Set NewCoverageCodes = (Selection.CurrentRegion)
> ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
> RefersTo:=NewCoverageCodes
>
> Range("A2:A" & NewCoverageCodeCount).Select
> Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
> ActiveWorkbook.Names.Add Name:="CoverageCodes", RefersTo:=CoverageCodes
>
> Range("B2:B" & NewCoverageCodeCount).Select
> Set CoverageMonths = (Selection.Range("B2:B" & NewCoverageCodeCount))
> ActiveWorkbook.Names.Add Name:="CoverageMonths",
> RefersTo:=CoverageMonths
>
> Range("C2:C" & NewCoverageCodeCount).Select
> Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
> ActiveWorkbook.Names.Add Name:="CoverageMiles", RefersTo:=CoverageMiles
>
> Range("D2" & NewCoverageCodeCount).Select
> Set CoverageEffectiveDate = (Selection.Range("C2:C" &
> NewCoverageCodeCount))
> ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
> RefersTo:=CoverageEffectiveDate
>
>
>
> --
>
> Joe Mac

All things are relative (in this situation anyway)...

If the NewCoverageCodes range is "C2:C7" then _
Range("NewCoverageCodes").Range("C2") is equal to the cell
in the second row, third column of CoverageMiles or cell E3.
And _
Range("NewCoverageCodes").Range("A1") is cell C2.

Also, subtract 1 (one) from the count when you specify the new range.
--
Jim Cone
Portland, Oregon USA




"Joe Mac" <(E-Mail Removed)> wrote in message news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
All...
I thank all in advance -

Attached is the code that I've built to identify a range and Define a name
for use in a workbook... As I step through the code it properely outlines
the range as is defined, however when I view the range in Excel it is not as
I expected...
The first named range always works - NewCoverageCodes, however after that
each named range is incorrect
CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
expected it to be 'Coverage Codes'!$A$2:$A$7 and
CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when I
expected it to be 'Coverage Codes'!$B$2:$B$7 and
CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
expected it to be 'Coverage Codes'!$C$2:$C$7 and

Sub AddCoverageCodes()
Dim NewCoverageCodes As Range
Dim CoverageCodes As Range
Dim CoverageMonths As Range
Dim CoverageMiles As Range
Dim CoverageEffectiveDate As Range
Dim i As Integer
Dim LoopCount As Long
Dim NewCoverageCodeCount As Long

Sheets("Coverage Codes").Select
Range("A1").Select
NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)

Selection.CurrentRegion.Select
Set NewCoverageCodes = (Selection.CurrentRegion)
ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
RefersTo:=NewCoverageCodes

Range("A2:A" & NewCoverageCodeCount).Select
Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageCodes", RefersTo:=CoverageCodes

Range("B2:B" & NewCoverageCodeCount).Select
Set CoverageMonths = (Selection.Range("B2:B" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMonths", RefersTo:=CoverageMonths

Range("C2:C" & NewCoverageCodeCount).Select
Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMiles", RefersTo:=CoverageMiles

Range("D2" & NewCoverageCodeCount).Select
Set CoverageEffectiveDate = (Selection.Range("C2:C" &
NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
RefersTo:=CoverageEffectiveDate



--

Joe Mac

Correction...
If the NewCoverageCodes range is "C2:C7" then _
Range("NewCoverageCodes").Range("C2") is equal to the cell
in the second row, third column of NewCoverageCodes or cell E3.
--
Jim Cone
Portland, Oregon USA

Thank you - This helped get me through this first hurdle... now on to the
next Looping step... I appreciate the assistance very much...
Joe
--

Joe Mac


"Don Guillett" wrote:

> You are probably incrementing your "new coverage code count" within the loop
>
> sub makenames()
> Sheets("Coverage Codes").Select
> NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count
>
> Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
> Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
> 'etc for c & d
>
> end sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Joe Mac" <(E-Mail Removed)> wrote in message
> news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
> > All...
> > I thank all in advance -
> >
> > Attached is the code that I've built to identify a range and Define a name
> > for use in a workbook... As I step through the code it properely outlines
> > the range as is defined, however when I view the range in Excel it is not
> > as
> > I expected...
> > The first named range always works - NewCoverageCodes, however after that
> > each named range is incorrect
> > CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
> > expected it to be 'Coverage Codes'!$A$2:$A$7 and
> > CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when I
> > expected it to be 'Coverage Codes'!$B$2:$B$7 and
> > CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
> > expected it to be 'Coverage Codes'!$C$2:$C$7 and
> >
> > Sub AddCoverageCodes()
> > Dim NewCoverageCodes As Range
> > Dim CoverageCodes As Range
> > Dim CoverageMonths As Range
> > Dim CoverageMiles As Range
> > Dim CoverageEffectiveDate As Range
> > Dim i As Integer
> > Dim LoopCount As Long
> > Dim NewCoverageCodeCount As Long
> >
> > Sheets("Coverage Codes").Select
> > Range("A1").Select
> > NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)
> >
> > Selection.CurrentRegion.Select
> > Set NewCoverageCodes = (Selection.CurrentRegion)
> > ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
> > RefersTo:=NewCoverageCodes
> >
> > Range("A2:A" & NewCoverageCodeCount).Select
> > Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
> > ActiveWorkbook.Names.Add Name:="CoverageCodes", RefersTo:=CoverageCodes
> >
> > Range("B2:B" & NewCoverageCodeCount).Select
> > Set CoverageMonths = (Selection.Range("B2:B" & NewCoverageCodeCount))
> > ActiveWorkbook.Names.Add Name:="CoverageMonths",
> > RefersTo:=CoverageMonths
> >
> > Range("C2:C" & NewCoverageCodeCount).Select
> > Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
> > ActiveWorkbook.Names.Add Name:="CoverageMiles", RefersTo:=CoverageMiles
> >
> > Range("D2" & NewCoverageCodeCount).Select
> > Set CoverageEffectiveDate = (Selection.Range("C2:C" &
> > NewCoverageCodeCount))
> > ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
> > RefersTo:=CoverageEffectiveDate
> >
> >
> >
> > --
> >
> > Joe Mac
>
>

What I sent was NOT a loop.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joe Mac" <(E-Mail Removed)> wrote in message
news:4FEDEAE7-FC11-441E-B41D-(E-Mail Removed)...
> Thank you - This helped get me through this first hurdle... now on to the
> next Looping step... I appreciate the assistance very much...
> Joe
> --
>
> Joe Mac
>
>
> "Don Guillett" wrote:
>
>> You are probably incrementing your "new coverage code count" within the
>> loop
>>
>> sub makenames()
>> Sheets("Coverage Codes").Select
>> NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count
>>
>> Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
>> Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
>> 'etc for c & d
>>
>> end sub
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Joe Mac" <(E-Mail Removed)> wrote in message
>> news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
>> > All...
>> > I thank all in advance -
>> >
>> > Attached is the code that I've built to identify a range and Define a
>> > name
>> > for use in a workbook... As I step through the code it properely
>> > outlines
>> > the range as is defined, however when I view the range in Excel it is
>> > not
>> > as
>> > I expected...
>> > The first named range always works - NewCoverageCodes, however after
>> > that
>> > each named range is incorrect
>> > CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
>> > expected it to be 'Coverage Codes'!$A$2:$A$7 and
>> > CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when
>> > I
>> > expected it to be 'Coverage Codes'!$B$2:$B$7 and
>> > CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
>> > expected it to be 'Coverage Codes'!$C$2:$C$7 and
>> >
>> > Sub AddCoverageCodes()
>> > Dim NewCoverageCodes As Range
>> > Dim CoverageCodes As Range
>> > Dim CoverageMonths As Range
>> > Dim CoverageMiles As Range
>> > Dim CoverageEffectiveDate As Range
>> > Dim i As Integer
>> > Dim LoopCount As Long
>> > Dim NewCoverageCodeCount As Long
>> >
>> > Sheets("Coverage Codes").Select
>> > Range("A1").Select
>> > NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)
>> >
>> > Selection.CurrentRegion.Select
>> > Set NewCoverageCodes = (Selection.CurrentRegion)
>> > ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
>> > RefersTo:=NewCoverageCodes
>> >
>> > Range("A2:A" & NewCoverageCodeCount).Select
>> > Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
>> > ActiveWorkbook.Names.Add Name:="CoverageCodes",
>> > RefersTo:=CoverageCodes
>> >
>> > Range("B2:B" & NewCoverageCodeCount).Select
>> > Set CoverageMonths = (Selection.Range("B2:B" &
>> > NewCoverageCodeCount))
>> > ActiveWorkbook.Names.Add Name:="CoverageMonths",
>> > RefersTo:=CoverageMonths
>> >
>> > Range("C2:C" & NewCoverageCodeCount).Select
>> > Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
>> > ActiveWorkbook.Names.Add Name:="CoverageMiles",
>> > RefersTo:=CoverageMiles
>> >
>> > Range("D2" & NewCoverageCodeCount).Select
>> > Set CoverageEffectiveDate = (Selection.Range("C2:C" &
>> > NewCoverageCodeCount))
>> > ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
>> > RefersTo:=CoverageEffectiveDate
>> >
>> >
>> >
>> > --
>> >
>> > Joe Mac
>>
>>

Hi Don...

Yes I know... The full extent of the program that I'm attempting to build
includes a Loop that will cycle through a range of data, "CoverageCodes" from
the earlier stream, and subsequently update a template range of data... the
updated template data will then be appended to create a Master Table...

I'm having troubles at this point the Loop appears to be defined as
infinite, it's just not hitting the target...

I've attached the Loop segment if you the time to review what I've defined
incorrectly, I'd be much appreciative..

LoopCount = 0

Do Until LoopCount > NewCoverageCodeCount

For i = 1 To i > TemplateRowCount
LoopCount = (LoopCount + 1)
Range("TemplateCoverageCode").Cells(i) =
Range("CoverageCode").Cells(i)
Range("TemplateCoverageMonths").Cells(i) =
Range("CoverageMonth").Cells(i)
Range("TemplateCoverageMiles").Cells(i) =
Range("CoverageMiles").Cells(i)
Range("TemplateCoverageEffectiveDate").Cells(i) =
Range("CoverageEffectiveDate").Cells(i)

Range("A3").Select
Selection.CurrentRegion.Select

Application.CopyObjectsWithCells = True
Selection.Copy

Sheets("Update Master").Select
MasterRowCount = ((Selection.CurrentRegion.Rows.Count) + 1)
Range("A" & MasterRowCount).Select
Application.ActiveCell.PasteSpecial Paste:=xlPasteAll
Sheets("Copy Template").Select
Range("A3").Select
Next
Loop




--

Joe Mac


"Don Guillett" wrote:

> What I sent was NOT a loop.....
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Joe Mac" <(E-Mail Removed)> wrote in message
> news:4FEDEAE7-FC11-441E-B41D-(E-Mail Removed)...
> > Thank you - This helped get me through this first hurdle... now on to the
> > next Looping step... I appreciate the assistance very much...
> > Joe
> > --
> >
> > Joe Mac
> >
> >
> > "Don Guillett" wrote:
> >
> >> You are probably incrementing your "new coverage code count" within the
> >> loop
> >>
> >> sub makenames()
> >> Sheets("Coverage Codes").Select
> >> NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count
> >>
> >> Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
> >> Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
> >> 'etc for c & d
> >>
> >> end sub
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Joe Mac" <(E-Mail Removed)> wrote in message
> >> news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
> >> > All...
> >> > I thank all in advance -
> >> >
> >> > Attached is the code that I've built to identify a range and Define a
> >> > name
> >> > for use in a workbook... As I step through the code it properely
> >> > outlines
> >> > the range as is defined, however when I view the range in Excel it is
> >> > not
> >> > as
> >> > I expected...
> >> > The first named range always works - NewCoverageCodes, however after
> >> > that
> >> > each named range is incorrect
> >> > CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
> >> > expected it to be 'Coverage Codes'!$A$2:$A$7 and
> >> > CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when
> >> > I
> >> > expected it to be 'Coverage Codes'!$B$2:$B$7 and
> >> > CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
> >> > expected it to be 'Coverage Codes'!$C$2:$C$7 and
> >> >
> >> > Sub AddCoverageCodes()
> >> > Dim NewCoverageCodes As Range
> >> > Dim CoverageCodes As Range
> >> > Dim CoverageMonths As Range
> >> > Dim CoverageMiles As Range
> >> > Dim CoverageEffectiveDate As Range
> >> > Dim i As Integer
> >> > Dim LoopCount As Long
> >> > Dim NewCoverageCodeCount As Long
> >> >
> >> > Sheets("Coverage Codes").Select
> >> > Range("A1").Select
> >> > NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)
> >> >
> >> > Selection.CurrentRegion.Select
> >> > Set NewCoverageCodes = (Selection.CurrentRegion)
> >> > ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
> >> > RefersTo:=NewCoverageCodes
> >> >
> >> > Range("A2:A" & NewCoverageCodeCount).Select
> >> > Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
> >> > ActiveWorkbook.Names.Add Name:="CoverageCodes",
> >> > RefersTo:=CoverageCodes
> >> >
> >> > Range("B2:B" & NewCoverageCodeCount).Select
> >> > Set CoverageMonths = (Selection.Range("B2:B" &
> >> > NewCoverageCodeCount))
> >> > ActiveWorkbook.Names.Add Name:="CoverageMonths",
> >> > RefersTo:=CoverageMonths
> >> >
> >> > Range("C2:C" & NewCoverageCodeCount).Select
> >> > Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
> >> > ActiveWorkbook.Names.Add Name:="CoverageMiles",
> >> > RefersTo:=CoverageMiles
> >> >
> >> > Range("D2" & NewCoverageCodeCount).Select
> >> > Set CoverageEffectiveDate = (Selection.Range("C2:C" &
> >> > NewCoverageCodeCount))
> >> > ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
> >> > RefersTo:=CoverageEffectiveDate
> >> >
> >> >
> >> >
> >> > --
> >> >
> >> > Joe Mac
> >>
> >>
>
>

Hard to tell from segments. If desired, send your wb to my address below
along with instructions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joe Mac" <(E-Mail Removed)> wrote in message
news:F95245C2-A632-4F76-B27C-(E-Mail Removed)...
> Hi Don...
>
> Yes I know... The full extent of the program that I'm attempting to build
> includes a Loop that will cycle through a range of data, "CoverageCodes"
> from
> the earlier stream, and subsequently update a template range of data...
> the
> updated template data will then be appended to create a Master Table...
>
> I'm having troubles at this point the Loop appears to be defined as
> infinite, it's just not hitting the target...
>
> I've attached the Loop segment if you the time to review what I've defined
> incorrectly, I'd be much appreciative..
>
> LoopCount = 0
>
> Do Until LoopCount > NewCoverageCodeCount
>
> For i = 1 To i > TemplateRowCount
> LoopCount = (LoopCount + 1)
> Range("TemplateCoverageCode").Cells(i) =
> Range("CoverageCode").Cells(i)
> Range("TemplateCoverageMonths").Cells(i) =
> Range("CoverageMonth").Cells(i)
> Range("TemplateCoverageMiles").Cells(i) =
> Range("CoverageMiles").Cells(i)
> Range("TemplateCoverageEffectiveDate").Cells(i) =
> Range("CoverageEffectiveDate").Cells(i)
>
> Range("A3").Select
> Selection.CurrentRegion.Select
>
> Application.CopyObjectsWithCells = True
> Selection.Copy
>
> Sheets("Update Master").Select
> MasterRowCount = ((Selection.CurrentRegion.Rows.Count) + 1)
> Range("A" & MasterRowCount).Select
> Application.ActiveCell.PasteSpecial Paste:=xlPasteAll
> Sheets("Copy Template").Select
> Range("A3").Select
> Next
> Loop
>
>
>
>
> --
>
> Joe Mac
>
>
> "Don Guillett" wrote:
>
>> What I sent was NOT a loop.....
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Joe Mac" <(E-Mail Removed)> wrote in message
>> news:4FEDEAE7-FC11-441E-B41D-(E-Mail Removed)...
>> > Thank you - This helped get me through this first hurdle... now on to
>> > the
>> > next Looping step... I appreciate the assistance very much...
>> > Joe
>> > --
>> >
>> > Joe Mac
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> You are probably incrementing your "new coverage code count" within
>> >> the
>> >> loop
>> >>
>> >> sub makenames()
>> >> Sheets("Coverage Codes").Select
>> >> NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count
>> >>
>> >> Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
>> >> Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
>> >> 'etc for c & d
>> >>
>> >> end sub
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Joe Mac" <(E-Mail Removed)> wrote in message
>> >> news:CB0488DB-62BE-4388-8F16-(E-Mail Removed)...
>> >> > All...
>> >> > I thank all in advance -
>> >> >
>> >> > Attached is the code that I've built to identify a range and Define
>> >> > a
>> >> > name
>> >> > for use in a workbook... As I step through the code it properely
>> >> > outlines
>> >> > the range as is defined, however when I view the range in Excel it
>> >> > is
>> >> > not
>> >> > as
>> >> > I expected...
>> >> > The first named range always works - NewCoverageCodes, however after
>> >> > that
>> >> > each named range is incorrect
>> >> > CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8
>> >> > when I
>> >> > expected it to be 'Coverage Codes'!$A$2:$A$7 and
>> >> > CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8
>> >> > when
>> >> > I
>> >> > expected it to be 'Coverage Codes'!$B$2:$B$7 and
>> >> > CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8
>> >> > when I
>> >> > expected it to be 'Coverage Codes'!$C$2:$C$7 and
>> >> >
>> >> > Sub AddCoverageCodes()
>> >> > Dim NewCoverageCodes As Range
>> >> > Dim CoverageCodes As Range
>> >> > Dim CoverageMonths As Range
>> >> > Dim CoverageMiles As Range
>> >> > Dim CoverageEffectiveDate As Range
>> >> > Dim i As Integer
>> >> > Dim LoopCount As Long
>> >> > Dim NewCoverageCodeCount As Long
>> >> >
>> >> > Sheets("Coverage Codes").Select
>> >> > Range("A1").Select
>> >> > NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)
>> >> >
>> >> > Selection.CurrentRegion.Select
>> >> > Set NewCoverageCodes = (Selection.CurrentRegion)
>> >> > ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
>> >> > RefersTo:=NewCoverageCodes
>> >> >
>> >> > Range("A2:A" & NewCoverageCodeCount).Select
>> >> > Set CoverageCodes = (Selection.Range("A2:A" &
>> >> > NewCoverageCodeCount))
>> >> > ActiveWorkbook.Names.Add Name:="CoverageCodes",
>> >> > RefersTo:=CoverageCodes
>> >> >
>> >> > Range("B2:B" & NewCoverageCodeCount).Select
>> >> > Set CoverageMonths = (Selection.Range("B2:B" &
>> >> > NewCoverageCodeCount))
>> >> > ActiveWorkbook.Names.Add Name:="CoverageMonths",
>> >> > RefersTo:=CoverageMonths
>> >> >
>> >> > Range("C2:C" & NewCoverageCodeCount).Select
>> >> > Set CoverageMiles = (Selection.Range("C2:C" &
>> >> > NewCoverageCodeCount))
>> >> > ActiveWorkbook.Names.Add Name:="CoverageMiles",
>> >> > RefersTo:=CoverageMiles
>> >> >
>> >> > Range("D2" & NewCoverageCodeCount).Select
>> >> > Set CoverageEffectiveDate = (Selection.Range("C2:C" &
>> >> > NewCoverageCodeCount))
>> >> > ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
>> >> > RefersTo:=CoverageEffectiveDate
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> > Joe Mac
>> >>
>> >>
>>
>>