Hi,

I'm sorry if this seems so trivial to you but I'm trying
to teach myself Visual Basic using the help facility and a
Microsoft Office2000 VBA Fundamentals book. The first
project I have set myself is to develop a financial
planner, the workbook contains 1 template worksheet, 1
worksheet containing reference data and one sheet per
month. My aim is to copy the template worksheet, rename it
for the current calendar month and define ranges on the
new sheet so values can be brought forward to reflect the
balance of my back account.

I am trying to define named RANGES (using VB code) to the
worksheet by passing 2 string variables to 2 of the
parameters to the ACTIVEWORKBOOK.NAMES.ADD command
The parameters being Name:
and RefersToRange:

At the moment the code gives the appearance of working
though the defined RANGES do not appear in the NAME BOX as
I would expect and therefore I can not access the defined
ranges. The defined RANGEs though do appear if I go to the
Insert drop down menu, select the Name option then Define
option!

The range DefineRanges contains 4 columns of the ranges to
be defined on the new worksheet
Column 1 - name of the range to be defined
e.g. "jandirectdebit" on the monthly worksheet
Column 2 - start of the RefersToR1C1 range
Column 3 - end of the RefersToR1C1 range
Column 4 - RefersTo information

The code reads:
Set r = range("DirectDebitName") 'details of ranges
to be defined
For i = 1 to r.rows.count
strRangeName = r.cells(i, 1)
strStartOfRange = r.cells(i, 2)
strEndOfRange = r.cells(i, 3)
strRefersTo = r.cells(i, 4)

ActiveWorkBook.Names.Add Name:strRangeName,
RefersToR1C1:=strStartOfRange & ":" & sreEndOfRange
Next i

I have also tried
ActiveWorkBook.Names.Add Name:strRangeName,
RefersTo:=strRefersTo

No matter what I try I can not define named ranges

HELP!!!