How to copy sheet with range names

[JFerry]

When I make a copy of a sheet in the same workbook, I loose all of the named ranges. I am an Excel user normally, and this works fine in Excel. i.e. when I copy the sheet, all named ranges are brought over into the new sheet.

 

[DaleWatson123321]

Hi JFerry,

I have to question your reference to being able to do this in Excel - i.e. copy a sheet and ALSO copy the named ranges.

I have never found this possible in Excel. And MOREOVER, I can't see any logic as to WHY one would want to have range names copied from one sheet to the next.

Specifically, one of the KEY reasons to use range names is that they UNIQUELY describe the cells or range-of-cells that you will want to reference by formulas or by code - thus you can reference them from ANYWHERE in the workbook.

I suggest that IF you were to have DUPLICATE names, this would only serve to COMPLICATE matters.

I just tested out your description of copying a sheet (with a range name) to another sheet. And the range name did NOT get copied. And, I must say, I am THANKFUL that this does NOT happen. If it did, then it would make for a FAR more difficult working environment.

Is it possible that you're referring to having separate WORKBOOKS loaded into memory, were you "could" copy one workbook to another - and ALSO copy the Range Names ???

Can you please double-check on your copying of sheets /workbooks in Excel ? Perhaps there's something I've missed. Your feedback would be appreciated.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JFerry]

We have a budgeting application written in APL (of all things) which produces "blank" Excel spreadsheets used for distributed input. These are then brought back into the application once they have been filled out. The input is made into the specific ranges which the APL system uses to locate the various data elements. We have written VBA code to copy the sheets, but even when I manually select Edit, Move or copy sheet in Excel, the range names do copy to the new sheet (tab), which is what we want to occur. We now have a cleint wanting to use Lotus as their distributed input tool, but can't get the copy to work the same way.

 

[DaleWatson123321]

JFerry,

I can agree that "MOVING" data or an entire sheet will also move the Range Names.

But NOT Copy. Is it possible that you have a later version of Excel than my '97, and that Microsoft has "changed the rules" (again) ???

As for creating the Range Names in Lotus 123, the following might be an option...

In your VBA code, add a routine that uses "Selection.ListNames". This will create a two column list of: range name, cell coordinates.

Then in Lotus 123, you can use this list to create the range names. I don't have Lotus 123 at work, but I seem to recall the command is {RANGE-NAME-CREATE celladdress}

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[german12]

I can fully agree to what Dale said, concerning unique range-names within a worksheet.
After this I tried the same as a sample in Excel 97:

1. created a worksheet (Sheet A) - named cell C3 as "myname" - and then made a short
calculation with this name into cell A5

2. created a 2nd sheet (Sheet B) - and copied all the contents from Sheet A to Sheet B.

Indeed - when I looked into the formular of cell A5 of sheet B I found again a the
reference-adress "myname" - but - and that is very important: all you do is, that you
reference to the SAME adress as you did in sheet A - so when you have a "5" there in
sheet A, also sheet B will use this contents ("5&quot.

If that is all you want, then you could create a worksheet A in Lotus, but only use
ABSOLUTE adresses (e.g. $myname) there - which will then be hold when you copy it to
Sheet B up to all sheets after B.... if you want.

I can not imagine that a new EXCEL can do it other- this would make all prior worksheets
in Excel not compatible and make it confusing to know, which worksheet is meant when you
refer to e.g. myname.

Regards from Germany

Klaus

btw
Please let me also know, which Excel-version is used by you.

 

[JFerry]

This is what we want, I think. The absolute address should work, because we really don't care what sheet the range is on, the import just looks for the range name "Amt" for example on any sheet, takes the value in that range, and fills in the data in the apl system, along with the other ranges on that sheet to make one record for that item. Thanks for both of your inputs. I use Excel 2000, but we have to provide for our clients using Excel 97 and up through XP. Thanks to Klaus and Dale.

Janis in Georgia

 

[german12]

It was a real pleasure to help - and believe me - I have very often had the similiar support within
the foxpro-forum, where Dale can also be found...**ggg
And do not forget - your team let us win in socker-world-championchip.

Should you have further questions concerning Lotus I could probably help...

greetings
klaus
kbriesemeister@t-online.de