copying cell names

How can I copy cell names from one spreadsheet to another?

Hi Al

Not sure what you mean with "cell names". If you are referring to the
contents of the cell, you can either select the cell, press <Ctrl><C>, go
where you want to paste, and press <Ctrl><V>, or you can go to where you want
it on the other sheet, type in = click on the source sheet, click on the
particular cell, and hit <Enter>
--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> How can I copy cell names from one spreadsheet to another?

By "cell names" I mean that I have defined certain cell names using
Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".

I now want to allocate the same cell name to cell A30 in workbook "XYZ".

Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
becuase I have many cells that are named in "ABC", and dozens of other
workbooks need the same cell names - I'm hoping that I can copy in some way a
name from one book to another. (note the named cells are not necessarily in
the same place in spreadsheet.)



"Kassie" wrote:

> Hi Al
>
> Not sure what you mean with "cell names". If you are referring to the
> contents of the cell, you can either select the cell, press <Ctrl><C>, go
> where you want to paste, and press <Ctrl><V>, or you can go to where you want
> it on the other sheet, type in = click on the source sheet, click on the
> particular cell, and hit <Enter>
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "Al" wrote:
>
> > How can I copy cell names from one spreadsheet to another?

Hi Al

You could use a macro to name the ranges in the other notebooks, but what
worries me is that you say they are not all in the same place? You will have
to pass some criteria to the macro, so that the range names are created with
the correct ranges!

If the macro must ask for ranges everytime it tries to create a range name,
you may as well do it yourself, without having to go the the effort of
writing code to do it!

If these range name referred to similar ranges in the various books, it
would be a breeze of course, to use a macro.

--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> By "cell names" I mean that I have defined certain cell names using
> Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
>
> I now want to allocate the same cell name to cell A30 in workbook "XYZ".
>
> Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> becuase I have many cells that are named in "ABC", and dozens of other
> workbooks need the same cell names - I'm hoping that I can copy in some way a
> name from one book to another. (note the named cells are not necessarily in
> the same place in spreadsheet.)
>
>
>
> "Kassie" wrote:
>
> > Hi Al
> >
> > Not sure what you mean with "cell names". If you are referring to the
> > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > it on the other sheet, type in = click on the source sheet, click on the
> > particular cell, and hit <Enter>
> > --
> > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> >
> >
> > "Al" wrote:
> >
> > > How can I copy cell names from one spreadsheet to another?

Thanks Kassie....I'll see what I can do with your help!

"Kassie" wrote:

> Hi Al
>
> You could use a macro to name the ranges in the other notebooks, but what
> worries me is that you say they are not all in the same place? You will have
> to pass some criteria to the macro, so that the range names are created with
> the correct ranges!
>
> If the macro must ask for ranges everytime it tries to create a range name,
> you may as well do it yourself, without having to go the the effort of
> writing code to do it!
>
> If these range name referred to similar ranges in the various books, it
> would be a breeze of course, to use a macro.
>
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "Al" wrote:
>
> > By "cell names" I mean that I have defined certain cell names using
> > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> >
> > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> >
> > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > becuase I have many cells that are named in "ABC", and dozens of other
> > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > name from one book to another. (note the named cells are not necessarily in
> > the same place in spreadsheet.)
> >
> >
> >
> > "Kassie" wrote:
> >
> > > Hi Al
> > >
> > > Not sure what you mean with "cell names". If you are referring to the
> > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > it on the other sheet, type in = click on the source sheet, click on the
> > > particular cell, and hit <Enter>
> > > --
> > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > >
> > >
> > > "Al" wrote:
> > >
> > > > How can I copy cell names from one spreadsheet to another?

Just wondering whether my macro can be written along the following lines:

A simple macro for Insert/Name/Define uses the current cell, i.e.
ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"

If I go to another cell, say R17C11, and run this macro, then it renames
cell R1C1 as "cellname1". Is there a way of having the macro use the "current
cell", something along the lines of....
ActiveWorkbook.Names.Add Name:="cellname1",
RefersTocurrentcell:="=Sheet1!currentcell"




"Al" wrote:

> Thanks Kassie....I'll see what I can do with your help!
>
> "Kassie" wrote:
>
> > Hi Al
> >
> > You could use a macro to name the ranges in the other notebooks, but what
> > worries me is that you say they are not all in the same place? You will have
> > to pass some criteria to the macro, so that the range names are created with
> > the correct ranges!
> >
> > If the macro must ask for ranges everytime it tries to create a range name,
> > you may as well do it yourself, without having to go the the effort of
> > writing code to do it!
> >
> > If these range name referred to similar ranges in the various books, it
> > would be a breeze of course, to use a macro.
> >
> > --
> > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> >
> >
> > "Al" wrote:
> >
> > > By "cell names" I mean that I have defined certain cell names using
> > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > >
> > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > >
> > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > becuase I have many cells that are named in "ABC", and dozens of other
> > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > name from one book to another. (note the named cells are not necessarily in
> > > the same place in spreadsheet.)
> > >
> > >
> > >
> > > "Kassie" wrote:
> > >
> > > > Hi Al
> > > >
> > > > Not sure what you mean with "cell names". If you are referring to the
> > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > particular cell, and hit <Enter>
> > > > --
> > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > >
> > > >
> > > > "Al" wrote:
> > > >
> > > > > How can I copy cell names from one spreadsheet to another?

Use the following to start with:

Sub CopyName()
Dim rngName As Variant
rngName = InputBox("Insert the Range Name you want to copy")
With ActiveCell
ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
End With
End Sub

Select the cell you want to name, type in the name, and it will create the
range in the active cell
--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> Just wondering whether my macro can be written along the following lines:
>
> A simple macro for Insert/Name/Define uses the current cell, i.e.
> ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
>
> If I go to another cell, say R17C11, and run this macro, then it renames
> cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> cell", something along the lines of....
> ActiveWorkbook.Names.Add Name:="cellname1",
> RefersTocurrentcell:="=Sheet1!currentcell"
>
>
>
>
> "Al" wrote:
>
> > Thanks Kassie....I'll see what I can do with your help!
> >
> > "Kassie" wrote:
> >
> > > Hi Al
> > >
> > > You could use a macro to name the ranges in the other notebooks, but what
> > > worries me is that you say they are not all in the same place? You will have
> > > to pass some criteria to the macro, so that the range names are created with
> > > the correct ranges!
> > >
> > > If the macro must ask for ranges everytime it tries to create a range name,
> > > you may as well do it yourself, without having to go the the effort of
> > > writing code to do it!
> > >
> > > If these range name referred to similar ranges in the various books, it
> > > would be a breeze of course, to use a macro.
> > >
> > > --
> > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > >
> > >
> > > "Al" wrote:
> > >
> > > > By "cell names" I mean that I have defined certain cell names using
> > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > >
> > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > >
> > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > name from one book to another. (note the named cells are not necessarily in
> > > > the same place in spreadsheet.)
> > > >
> > > >
> > > >
> > > > "Kassie" wrote:
> > > >
> > > > > Hi Al
> > > > >
> > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > particular cell, and hit <Enter>
> > > > > --
> > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > >
> > > > >
> > > > > "Al" wrote:
> > > > >
> > > > > > How can I copy cell names from one spreadsheet to another?

Hi Kassie,
I'm back from a holiday, hence the lack of follow up.....

I'm not sure that I have explained my question properly...reading your
advice I think it is not what I need, so let my further ask as follows:

In an existing spreadsheet I have cell names: name 1, name2, etc..there are
24 named cells.
I now want to allocate these same names to many other (old) spreadsheets.
Your most recent help advises to "Select the cell i want to name, type in the
name..." This requires that I type in the name into the 24 cells of each
(old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
paste the cell name. (If I must type in the cell name, then I may as well
just use "Insert/Name/Define")

Or have I misunderstood your advice?

Allan
"Kassie" wrote:

> Use the following to start with:
>
> Sub CopyName()
> Dim rngName As Variant
> rngName = InputBox("Insert the Range Name you want to copy")
> With ActiveCell
> ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> End With
> End Sub
>
> Select the cell you want to name, type in the name, and it will create the
> range in the active cell
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "Al" wrote:
>
> > Just wondering whether my macro can be written along the following lines:
> >
> > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> >
> > If I go to another cell, say R17C11, and run this macro, then it renames
> > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > cell", something along the lines of....
> > ActiveWorkbook.Names.Add Name:="cellname1",
> > RefersTocurrentcell:="=Sheet1!currentcell"
> >
> >
> >
> >
> > "Al" wrote:
> >
> > > Thanks Kassie....I'll see what I can do with your help!
> > >
> > > "Kassie" wrote:
> > >
> > > > Hi Al
> > > >
> > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > worries me is that you say they are not all in the same place? You will have
> > > > to pass some criteria to the macro, so that the range names are created with
> > > > the correct ranges!
> > > >
> > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > you may as well do it yourself, without having to go the the effort of
> > > > writing code to do it!
> > > >
> > > > If these range name referred to similar ranges in the various books, it
> > > > would be a breeze of course, to use a macro.
> > > >
> > > > --
> > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > >
> > > >
> > > > "Al" wrote:
> > > >
> > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > >
> > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > >
> > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > the same place in spreadsheet.)
> > > > >
> > > > >
> > > > >
> > > > > "Kassie" wrote:
> > > > >
> > > > > > Hi Al
> > > > > >
> > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > particular cell, and hit <Enter>
> > > > > > --
> > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > >
> > > > > >
> > > > > > "Al" wrote:
> > > > > >
> > > > > > > How can I copy cell names from one spreadsheet to another?

Hi Al

Not necessarily, no. The problem is that I do not know whether you want to
use the exact cell addresses to create these new range names, nor do I know
the location of the other sheets. What I suggested, is merely a starting
point to work from, which is already faster than Insert|Name|Define, as you
can use <Ctrl><n> or something to that effect, to run the macro.

You can sequence through the workbooks, provided that you put them all in
one directory or folder, by creating a FOR DO loop, to do the name creation
routine x number of times, if the range names are all in the same cells.
You can also take a workbook, create all the required names, save, and move
onto the next workbook, where the whole routine will be repeated until there
are no more files in the folder.

However, for me to do that, I will need to know a lot more than I do at
present. As I said, I do not have the names, I do not have the cell
addresses, and I do not know whether the workbooks are going to have the new
range names in the same cell addresses as the new book. You see, there has
to be method in our madness, for PC's to do our work:-)

You can however expand on my suggestion, you can for example code in the
range names and addresses where you want to create these, you can write a
file save routine into the procedure, to save every file where you have
created names, and you can let the macro open the next file.

If for example you are going to put all the files in one folder, you can use
the command ChDir("New Folder Name") to go to the specified directory, or
merely put the folder name in a variable.

Use a variable to hold the description of files you want to open, eg fType
as a string variable, and a variable to hold the file names, such as fName
Insert a statement that checks for files, such as

Dim fName as string
Dim targetDir as String
Dim fType as String
targetDir = 'insert the target directory or folder
fType = "*.xls"
fName = Dir(TargetDir & "\" & FileType
If fName = "", Then
MsgBox "No files matching " & fName
'and after that a statement to open the file, such as
Else
Do
Workbooks.Open filename:=fName

'do your thing
'Save the file
ActiveWorkbook.Save
'close the workbook
ActiveWorkbook.Close
fName = Dir()
loop until fName = ""
End If
Exit sub

Play with this, and post back if you have a problem!

--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> Hi Kassie,
> I'm back from a holiday, hence the lack of follow up.....
>
> I'm not sure that I have explained my question properly...reading your
> advice I think it is not what I need, so let my further ask as follows:
>
> In an existing spreadsheet I have cell names: name 1, name2, etc..there are
> 24 named cells.
> I now want to allocate these same names to many other (old) spreadsheets.
> Your most recent help advises to "Select the cell i want to name, type in the
> name..." This requires that I type in the name into the 24 cells of each
> (old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
> paste the cell name. (If I must type in the cell name, then I may as well
> just use "Insert/Name/Define")
>
> Or have I misunderstood your advice?
>
> Allan
> "Kassie" wrote:
>
> > Use the following to start with:
> >
> > Sub CopyName()
> > Dim rngName As Variant
> > rngName = InputBox("Insert the Range Name you want to copy")
> > With ActiveCell
> > ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> > End With
> > End Sub
> >
> > Select the cell you want to name, type in the name, and it will create the
> > range in the active cell
> > --
> > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> >
> >
> > "Al" wrote:
> >
> > > Just wondering whether my macro can be written along the following lines:
> > >
> > > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> > >
> > > If I go to another cell, say R17C11, and run this macro, then it renames
> > > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > > cell", something along the lines of....
> > > ActiveWorkbook.Names.Add Name:="cellname1",
> > > RefersTocurrentcell:="=Sheet1!currentcell"
> > >
> > >
> > >
> > >
> > > "Al" wrote:
> > >
> > > > Thanks Kassie....I'll see what I can do with your help!
> > > >
> > > > "Kassie" wrote:
> > > >
> > > > > Hi Al
> > > > >
> > > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > > worries me is that you say they are not all in the same place? You will have
> > > > > to pass some criteria to the macro, so that the range names are created with
> > > > > the correct ranges!
> > > > >
> > > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > > you may as well do it yourself, without having to go the the effort of
> > > > > writing code to do it!
> > > > >
> > > > > If these range name referred to similar ranges in the various books, it
> > > > > would be a breeze of course, to use a macro.
> > > > >
> > > > > --
> > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > >
> > > > >
> > > > > "Al" wrote:
> > > > >
> > > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > > >
> > > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > > >
> > > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > > the same place in spreadsheet.)
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Kassie" wrote:
> > > > > >
> > > > > > > Hi Al
> > > > > > >
> > > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > > particular cell, and hit <Enter>
> > > > > > > --
> > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > >
> > > > > > >
> > > > > > > "Al" wrote:
> > > > > > >
> > > > > > > > How can I copy cell names from one spreadsheet to another?

Kassie,

Thanks for all your help to-date. I am certainly getting in over my depth
now as I have no knowledge to speak of of the programming that you advise!
(VBA, macro writing, ....whatever!)

To answer you various q's re my spreadsheets:
i - the cells that I wish to name are all at different, and varying, cell
addresses in the older spreadsheets
ii - the spreadsheets are all in the same net work, but in different
directories
iii - once all my older spreadsheets have the named cells, then henceforth
all new spreadsheets will have the same named cells in the same cell address.
iv - the names are, for example, sub_ttl_electrical_hours and
sub_ttl_electrical_cost

In any case, I have probably "overstayed my welcome" with all these
questions flying back and forward. I am not going to learn enough about
programming over the next few days to enable me to fully utilise your help,
so I think I will have to result to naming each cell that needs a name in the
older spreadsheets using Insert|Name|Define!

I hope that this outcome will not deter you from offering further help to
those in need in the future!! Many thanks or all your efforts!


"Kassie" wrote:

> Hi Al
>
> Not necessarily, no. The problem is that I do not know whether you want to
> use the exact cell addresses to create these new range names, nor do I know
> the location of the other sheets. What I suggested, is merely a starting
> point to work from, which is already faster than Insert|Name|Define, as you
> can use <Ctrl><n> or something to that effect, to run the macro.
>
> You can sequence through the workbooks, provided that you put them all in
> one directory or folder, by creating a FOR DO loop, to do the name creation
> routine x number of times, if the range names are all in the same cells.
> You can also take a workbook, create all the required names, save, and move
> onto the next workbook, where the whole routine will be repeated until there
> are no more files in the folder.
>
> However, for me to do that, I will need to know a lot more than I do at
> present. As I said, I do not have the names, I do not have the cell
> addresses, and I do not know whether the workbooks are going to have the new
> range names in the same cell addresses as the new book. You see, there has
> to be method in our madness, for PC's to do our work:-)
>
> You can however expand on my suggestion, you can for example code in the
> range names and addresses where you want to create these, you can write a
> file save routine into the procedure, to save every file where you have
> created names, and you can let the macro open the next file.
>
> If for example you are going to put all the files in one folder, you can use
> the command ChDir("New Folder Name") to go to the specified directory, or
> merely put the folder name in a variable.
>
> Use a variable to hold the description of files you want to open, eg fType
> as a string variable, and a variable to hold the file names, such as fName
> Insert a statement that checks for files, such as
>
> Dim fName as string
> Dim targetDir as String
> Dim fType as String
> targetDir = 'insert the target directory or folder
> fType = "*.xls"
> fName = Dir(TargetDir & "\" & FileType
> If fName = "", Then
> MsgBox "No files matching " & fName
> 'and after that a statement to open the file, such as
> Else
> Do
> Workbooks.Open filename:=fName
>
> 'do your thing
> 'Save the file
> ActiveWorkbook.Save
> 'close the workbook
> ActiveWorkbook.Close
> fName = Dir()
> loop until fName = ""
> End If
> Exit sub
>
> Play with this, and post back if you have a problem!
>
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "Al" wrote:
>
> > Hi Kassie,
> > I'm back from a holiday, hence the lack of follow up.....
> >
> > I'm not sure that I have explained my question properly...reading your
> > advice I think it is not what I need, so let my further ask as follows:
> >
> > In an existing spreadsheet I have cell names: name 1, name2, etc..there are
> > 24 named cells.
> > I now want to allocate these same names to many other (old) spreadsheets.
> > Your most recent help advises to "Select the cell i want to name, type in the
> > name..." This requires that I type in the name into the 24 cells of each
> > (old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
> > paste the cell name. (If I must type in the cell name, then I may as well
> > just use "Insert/Name/Define")
> >
> > Or have I misunderstood your advice?
> >
> > Allan
> > "Kassie" wrote:
> >
> > > Use the following to start with:
> > >
> > > Sub CopyName()
> > > Dim rngName As Variant
> > > rngName = InputBox("Insert the Range Name you want to copy")
> > > With ActiveCell
> > > ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> > > End With
> > > End Sub
> > >
> > > Select the cell you want to name, type in the name, and it will create the
> > > range in the active cell
> > > --
> > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > >
> > >
> > > "Al" wrote:
> > >
> > > > Just wondering whether my macro can be written along the following lines:
> > > >
> > > > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > > > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> > > >
> > > > If I go to another cell, say R17C11, and run this macro, then it renames
> > > > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > > > cell", something along the lines of....
> > > > ActiveWorkbook.Names.Add Name:="cellname1",
> > > > RefersTocurrentcell:="=Sheet1!currentcell"
> > > >
> > > >
> > > >
> > > >
> > > > "Al" wrote:
> > > >
> > > > > Thanks Kassie....I'll see what I can do with your help!
> > > > >
> > > > > "Kassie" wrote:
> > > > >
> > > > > > Hi Al
> > > > > >
> > > > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > > > worries me is that you say they are not all in the same place? You will have
> > > > > > to pass some criteria to the macro, so that the range names are created with
> > > > > > the correct ranges!
> > > > > >
> > > > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > > > you may as well do it yourself, without having to go the the effort of
> > > > > > writing code to do it!
> > > > > >
> > > > > > If these range name referred to similar ranges in the various books, it
> > > > > > would be a breeze of course, to use a macro.
> > > > > >
> > > > > > --
> > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > >
> > > > > >
> > > > > > "Al" wrote:
> > > > > >
> > > > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > > > >
> > > > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > > > >
> > > > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > > > the same place in spreadsheet.)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Kassie" wrote:
> > > > > > >
> > > > > > > > Hi Al
> > > > > > > >
> > > > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > > > particular cell, and hit <Enter>
> > > > > > > > --
> > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > >
> > > > > > > >
> > > > > > > > "Al" wrote:
> > > > > > > >
> > > > > > > > > How can I copy cell names from one spreadsheet to another?

Hi Al

You have definitely not overstayed your welcome!

However, if your range names are all in different locations throughout the
various spreadsheet, then I am afraid the long-winded way is the only way!
However, you can create a template for all new sheets, already having all the
required range names in the correct locations. That means that every time
you want to create a new sheet, you will open the template, and save it with
the required name. That in itself will save you a lot of effort!

Best of luck with your project!

--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> Kassie,
>
> Thanks for all your help to-date. I am certainly getting in over my depth
> now as I have no knowledge to speak of of the programming that you advise!
> (VBA, macro writing, ....whatever!)
>
> To answer you various q's re my spreadsheets:
> i - the cells that I wish to name are all at different, and varying, cell
> addresses in the older spreadsheets
> ii - the spreadsheets are all in the same net work, but in different
> directories
> iii - once all my older spreadsheets have the named cells, then henceforth
> all new spreadsheets will have the same named cells in the same cell address.
> iv - the names are, for example, sub_ttl_electrical_hours and
> sub_ttl_electrical_cost
>
> In any case, I have probably "overstayed my welcome" with all these
> questions flying back and forward. I am not going to learn enough about
> programming over the next few days to enable me to fully utilise your help,
> so I think I will have to result to naming each cell that needs a name in the
> older spreadsheets using Insert|Name|Define!
>
> I hope that this outcome will not deter you from offering further help to
> those in need in the future!! Many thanks or all your efforts!
>
>
> "Kassie" wrote:
>
> > Hi Al
> >
> > Not necessarily, no. The problem is that I do not know whether you want to
> > use the exact cell addresses to create these new range names, nor do I know
> > the location of the other sheets. What I suggested, is merely a starting
> > point to work from, which is already faster than Insert|Name|Define, as you
> > can use <Ctrl><n> or something to that effect, to run the macro.
> >
> > You can sequence through the workbooks, provided that you put them all in
> > one directory or folder, by creating a FOR DO loop, to do the name creation
> > routine x number of times, if the range names are all in the same cells.
> > You can also take a workbook, create all the required names, save, and move
> > onto the next workbook, where the whole routine will be repeated until there
> > are no more files in the folder.
> >
> > However, for me to do that, I will need to know a lot more than I do at
> > present. As I said, I do not have the names, I do not have the cell
> > addresses, and I do not know whether the workbooks are going to have the new
> > range names in the same cell addresses as the new book. You see, there has
> > to be method in our madness, for PC's to do our work:-)
> >
> > You can however expand on my suggestion, you can for example code in the
> > range names and addresses where you want to create these, you can write a
> > file save routine into the procedure, to save every file where you have
> > created names, and you can let the macro open the next file.
> >
> > If for example you are going to put all the files in one folder, you can use
> > the command ChDir("New Folder Name") to go to the specified directory, or
> > merely put the folder name in a variable.
> >
> > Use a variable to hold the description of files you want to open, eg fType
> > as a string variable, and a variable to hold the file names, such as fName
> > Insert a statement that checks for files, such as
> >
> > Dim fName as string
> > Dim targetDir as String
> > Dim fType as String
> > targetDir = 'insert the target directory or folder
> > fType = "*.xls"
> > fName = Dir(TargetDir & "\" & FileType
> > If fName = "", Then
> > MsgBox "No files matching " & fName
> > 'and after that a statement to open the file, such as
> > Else
> > Do
> > Workbooks.Open filename:=fName
> >
> > 'do your thing
> > 'Save the file
> > ActiveWorkbook.Save
> > 'close the workbook
> > ActiveWorkbook.Close
> > fName = Dir()
> > loop until fName = ""
> > End If
> > Exit sub
> >
> > Play with this, and post back if you have a problem!
> >
> > --
> > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> >
> >
> > "Al" wrote:
> >
> > > Hi Kassie,
> > > I'm back from a holiday, hence the lack of follow up.....
> > >
> > > I'm not sure that I have explained my question properly...reading your
> > > advice I think it is not what I need, so let my further ask as follows:
> > >
> > > In an existing spreadsheet I have cell names: name 1, name2, etc..there are
> > > 24 named cells.
> > > I now want to allocate these same names to many other (old) spreadsheets.
> > > Your most recent help advises to "Select the cell i want to name, type in the
> > > name..." This requires that I type in the name into the 24 cells of each
> > > (old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
> > > paste the cell name. (If I must type in the cell name, then I may as well
> > > just use "Insert/Name/Define")
> > >
> > > Or have I misunderstood your advice?
> > >
> > > Allan
> > > "Kassie" wrote:
> > >
> > > > Use the following to start with:
> > > >
> > > > Sub CopyName()
> > > > Dim rngName As Variant
> > > > rngName = InputBox("Insert the Range Name you want to copy")
> > > > With ActiveCell
> > > > ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> > > > End With
> > > > End Sub
> > > >
> > > > Select the cell you want to name, type in the name, and it will create the
> > > > range in the active cell
> > > > --
> > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > >
> > > >
> > > > "Al" wrote:
> > > >
> > > > > Just wondering whether my macro can be written along the following lines:
> > > > >
> > > > > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > > > > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> > > > >
> > > > > If I go to another cell, say R17C11, and run this macro, then it renames
> > > > > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > > > > cell", something along the lines of....
> > > > > ActiveWorkbook.Names.Add Name:="cellname1",
> > > > > RefersTocurrentcell:="=Sheet1!currentcell"
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Al" wrote:
> > > > >
> > > > > > Thanks Kassie....I'll see what I can do with your help!
> > > > > >
> > > > > > "Kassie" wrote:
> > > > > >
> > > > > > > Hi Al
> > > > > > >
> > > > > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > > > > worries me is that you say they are not all in the same place? You will have
> > > > > > > to pass some criteria to the macro, so that the range names are created with
> > > > > > > the correct ranges!
> > > > > > >
> > > > > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > > > > you may as well do it yourself, without having to go the the effort of
> > > > > > > writing code to do it!
> > > > > > >
> > > > > > > If these range name referred to similar ranges in the various books, it
> > > > > > > would be a breeze of course, to use a macro.
> > > > > > >
> > > > > > > --
> > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > >
> > > > > > >
> > > > > > > "Al" wrote:
> > > > > > >
> > > > > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > > > > >
> > > > > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > > > > >
> > > > > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > > > > the same place in spreadsheet.)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Kassie" wrote:
> > > > > > > >
> > > > > > > > > Hi Al
> > > > > > > > >
> > > > > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > > > > particular cell, and hit <Enter>
> > > > > > > > > --
> > > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Al" wrote:
> > > > > > > > >
> > > > > > > > > > How can I copy cell names from one spreadsheet to another?

Thanks Kassie,

The "template" idea is exactly where I'm headed, the only snag is that
there're dozens of older spreadsheets with the data that I need....hence my
original question!

Ah, well, sometimes the long winded way ends up being the only way!

One final question, and on a different note, where's a good place to start
learning the "programming" that you suggested earlier...I think I need to
acquire some basics along these lines?

Al

"Kassie" wrote:

> Hi Al
>
> You have definitely not overstayed your welcome!
>
> However, if your range names are all in different locations throughout the
> various spreadsheet, then I am afraid the long-winded way is the only way!
> However, you can create a template for all new sheets, already having all the
> required range names in the correct locations. That means that every time
> you want to create a new sheet, you will open the template, and save it with
> the required name. That in itself will save you a lot of effort!
>
> Best of luck with your project!
>
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "Al" wrote:
>
> > Kassie,
> >
> > Thanks for all your help to-date. I am certainly getting in over my depth
> > now as I have no knowledge to speak of of the programming that you advise!
> > (VBA, macro writing, ....whatever!)
> >
> > To answer you various q's re my spreadsheets:
> > i - the cells that I wish to name are all at different, and varying, cell
> > addresses in the older spreadsheets
> > ii - the spreadsheets are all in the same net work, but in different
> > directories
> > iii - once all my older spreadsheets have the named cells, then henceforth
> > all new spreadsheets will have the same named cells in the same cell address.
> > iv - the names are, for example, sub_ttl_electrical_hours and
> > sub_ttl_electrical_cost
> >
> > In any case, I have probably "overstayed my welcome" with all these
> > questions flying back and forward. I am not going to learn enough about
> > programming over the next few days to enable me to fully utilise your help,
> > so I think I will have to result to naming each cell that needs a name in the
> > older spreadsheets using Insert|Name|Define!
> >
> > I hope that this outcome will not deter you from offering further help to
> > those in need in the future!! Many thanks or all your efforts!
> >
> >
> > "Kassie" wrote:
> >
> > > Hi Al
> > >
> > > Not necessarily, no. The problem is that I do not know whether you want to
> > > use the exact cell addresses to create these new range names, nor do I know
> > > the location of the other sheets. What I suggested, is merely a starting
> > > point to work from, which is already faster than Insert|Name|Define, as you
> > > can use <Ctrl><n> or something to that effect, to run the macro.
> > >
> > > You can sequence through the workbooks, provided that you put them all in
> > > one directory or folder, by creating a FOR DO loop, to do the name creation
> > > routine x number of times, if the range names are all in the same cells.
> > > You can also take a workbook, create all the required names, save, and move
> > > onto the next workbook, where the whole routine will be repeated until there
> > > are no more files in the folder.
> > >
> > > However, for me to do that, I will need to know a lot more than I do at
> > > present. As I said, I do not have the names, I do not have the cell
> > > addresses, and I do not know whether the workbooks are going to have the new
> > > range names in the same cell addresses as the new book. You see, there has
> > > to be method in our madness, for PC's to do our work:-)
> > >
> > > You can however expand on my suggestion, you can for example code in the
> > > range names and addresses where you want to create these, you can write a
> > > file save routine into the procedure, to save every file where you have
> > > created names, and you can let the macro open the next file.
> > >
> > > If for example you are going to put all the files in one folder, you can use
> > > the command ChDir("New Folder Name") to go to the specified directory, or
> > > merely put the folder name in a variable.
> > >
> > > Use a variable to hold the description of files you want to open, eg fType
> > > as a string variable, and a variable to hold the file names, such as fName
> > > Insert a statement that checks for files, such as
> > >
> > > Dim fName as string
> > > Dim targetDir as String
> > > Dim fType as String
> > > targetDir = 'insert the target directory or folder
> > > fType = "*.xls"
> > > fName = Dir(TargetDir & "\" & FileType
> > > If fName = "", Then
> > > MsgBox "No files matching " & fName
> > > 'and after that a statement to open the file, such as
> > > Else
> > > Do
> > > Workbooks.Open filename:=fName
> > >
> > > 'do your thing
> > > 'Save the file
> > > ActiveWorkbook.Save
> > > 'close the workbook
> > > ActiveWorkbook.Close
> > > fName = Dir()
> > > loop until fName = ""
> > > End If
> > > Exit sub
> > >
> > > Play with this, and post back if you have a problem!
> > >
> > > --
> > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > >
> > >
> > > "Al" wrote:
> > >
> > > > Hi Kassie,
> > > > I'm back from a holiday, hence the lack of follow up.....
> > > >
> > > > I'm not sure that I have explained my question properly...reading your
> > > > advice I think it is not what I need, so let my further ask as follows:
> > > >
> > > > In an existing spreadsheet I have cell names: name 1, name2, etc..there are
> > > > 24 named cells.
> > > > I now want to allocate these same names to many other (old) spreadsheets.
> > > > Your most recent help advises to "Select the cell i want to name, type in the
> > > > name..." This requires that I type in the name into the 24 cells of each
> > > > (old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
> > > > paste the cell name. (If I must type in the cell name, then I may as well
> > > > just use "Insert/Name/Define")
> > > >
> > > > Or have I misunderstood your advice?
> > > >
> > > > Allan
> > > > "Kassie" wrote:
> > > >
> > > > > Use the following to start with:
> > > > >
> > > > > Sub CopyName()
> > > > > Dim rngName As Variant
> > > > > rngName = InputBox("Insert the Range Name you want to copy")
> > > > > With ActiveCell
> > > > > ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> > > > > End With
> > > > > End Sub
> > > > >
> > > > > Select the cell you want to name, type in the name, and it will create the
> > > > > range in the active cell
> > > > > --
> > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > >
> > > > >
> > > > > "Al" wrote:
> > > > >
> > > > > > Just wondering whether my macro can be written along the following lines:
> > > > > >
> > > > > > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > > > > > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> > > > > >
> > > > > > If I go to another cell, say R17C11, and run this macro, then it renames
> > > > > > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > > > > > cell", something along the lines of....
> > > > > > ActiveWorkbook.Names.Add Name:="cellname1",
> > > > > > RefersTocurrentcell:="=Sheet1!currentcell"
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Al" wrote:
> > > > > >
> > > > > > > Thanks Kassie....I'll see what I can do with your help!
> > > > > > >
> > > > > > > "Kassie" wrote:
> > > > > > >
> > > > > > > > Hi Al
> > > > > > > >
> > > > > > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > > > > > worries me is that you say they are not all in the same place? You will have
> > > > > > > > to pass some criteria to the macro, so that the range names are created with
> > > > > > > > the correct ranges!
> > > > > > > >
> > > > > > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > > > > > you may as well do it yourself, without having to go the the effort of
> > > > > > > > writing code to do it!
> > > > > > > >
> > > > > > > > If these range name referred to similar ranges in the various books, it
> > > > > > > > would be a breeze of course, to use a macro.
> > > > > > > >
> > > > > > > > --
> > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > >
> > > > > > > >
> > > > > > > > "Al" wrote:
> > > > > > > >
> > > > > > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > > > > > >
> > > > > > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > > > > > >
> > > > > > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > > > > > the same place in spreadsheet.)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Kassie" wrote:
> > > > > > > > >
> > > > > > > > > > Hi Al
> > > > > > > > > >
> > > > > > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > > > > > particular cell, and hit <Enter>
> > > > > > > > > > --
> > > > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Al" wrote:
> > > > > > > > > >
> > > > > > > > > > > How can I copy cell names from one spreadsheet to another?

Hi Al

Great idea. Lots of books on the subject, and this forum is a very good
source of information and help. The main idea is to decide what you want to
achieve, and then take it from there. A good site to visit for starters are:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

There are also stacks of others, such as:
www.cpearson.com - excel;
www.mvps.org-dmcritchie-excel;
www.rondebruin.nl;
www.mcgimpsey.com
www.j-walk.com-ss-exceltips;
www.peltiertech.com-excel.

And then there's Google of course.

I hope you enjoy the experience, and learn something very useful about Excel
and VBA, which will stand you in good stead!

--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"Al" wrote:

> Thanks Kassie,
>
> The "template" idea is exactly where I'm headed, the only snag is that
> there're dozens of older spreadsheets with the data that I need....hence my
> original question!
>
> Ah, well, sometimes the long winded way ends up being the only way!
>
> One final question, and on a different note, where's a good place to start
> learning the "programming" that you suggested earlier...I think I need to
> acquire some basics along these lines?
>
> Al
>
> "Kassie" wrote:
>
> > Hi Al
> >
> > You have definitely not overstayed your welcome!
> >
> > However, if your range names are all in different locations throughout the
> > various spreadsheet, then I am afraid the long-winded way is the only way!
> > However, you can create a template for all new sheets, already having all the
> > required range names in the correct locations. That means that every time
> > you want to create a new sheet, you will open the template, and save it with
> > the required name. That in itself will save you a lot of effort!
> >
> > Best of luck with your project!
> >
> > --
> > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> >
> >
> > "Al" wrote:
> >
> > > Kassie,
> > >
> > > Thanks for all your help to-date. I am certainly getting in over my depth
> > > now as I have no knowledge to speak of of the programming that you advise!
> > > (VBA, macro writing, ....whatever!)
> > >
> > > To answer you various q's re my spreadsheets:
> > > i - the cells that I wish to name are all at different, and varying, cell
> > > addresses in the older spreadsheets
> > > ii - the spreadsheets are all in the same net work, but in different
> > > directories
> > > iii - once all my older spreadsheets have the named cells, then henceforth
> > > all new spreadsheets will have the same named cells in the same cell address.
> > > iv - the names are, for example, sub_ttl_electrical_hours and
> > > sub_ttl_electrical_cost
> > >
> > > In any case, I have probably "overstayed my welcome" with all these
> > > questions flying back and forward. I am not going to learn enough about
> > > programming over the next few days to enable me to fully utilise your help,
> > > so I think I will have to result to naming each cell that needs a name in the
> > > older spreadsheets using Insert|Name|Define!
> > >
> > > I hope that this outcome will not deter you from offering further help to
> > > those in need in the future!! Many thanks or all your efforts!
> > >
> > >
> > > "Kassie" wrote:
> > >
> > > > Hi Al
> > > >
> > > > Not necessarily, no. The problem is that I do not know whether you want to
> > > > use the exact cell addresses to create these new range names, nor do I know
> > > > the location of the other sheets. What I suggested, is merely a starting
> > > > point to work from, which is already faster than Insert|Name|Define, as you
> > > > can use <Ctrl><n> or something to that effect, to run the macro.
> > > >
> > > > You can sequence through the workbooks, provided that you put them all in
> > > > one directory or folder, by creating a FOR DO loop, to do the name creation
> > > > routine x number of times, if the range names are all in the same cells.
> > > > You can also take a workbook, create all the required names, save, and move
> > > > onto the next workbook, where the whole routine will be repeated until there
> > > > are no more files in the folder.
> > > >
> > > > However, for me to do that, I will need to know a lot more than I do at
> > > > present. As I said, I do not have the names, I do not have the cell
> > > > addresses, and I do not know whether the workbooks are going to have the new
> > > > range names in the same cell addresses as the new book. You see, there has
> > > > to be method in our madness, for PC's to do our work:-)
> > > >
> > > > You can however expand on my suggestion, you can for example code in the
> > > > range names and addresses where you want to create these, you can write a
> > > > file save routine into the procedure, to save every file where you have
> > > > created names, and you can let the macro open the next file.
> > > >
> > > > If for example you are going to put all the files in one folder, you can use
> > > > the command ChDir("New Folder Name") to go to the specified directory, or
> > > > merely put the folder name in a variable.
> > > >
> > > > Use a variable to hold the description of files you want to open, eg fType
> > > > as a string variable, and a variable to hold the file names, such as fName
> > > > Insert a statement that checks for files, such as
> > > >
> > > > Dim fName as string
> > > > Dim targetDir as String
> > > > Dim fType as String
> > > > targetDir = 'insert the target directory or folder
> > > > fType = "*.xls"
> > > > fName = Dir(TargetDir & "\" & FileType
> > > > If fName = "", Then
> > > > MsgBox "No files matching " & fName
> > > > 'and after that a statement to open the file, such as
> > > > Else
> > > > Do
> > > > Workbooks.Open filename:=fName
> > > >
> > > > 'do your thing
> > > > 'Save the file
> > > > ActiveWorkbook.Save
> > > > 'close the workbook
> > > > ActiveWorkbook.Close
> > > > fName = Dir()
> > > > loop until fName = ""
> > > > End If
> > > > Exit sub
> > > >
> > > > Play with this, and post back if you have a problem!
> > > >
> > > > --
> > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > >
> > > >
> > > > "Al" wrote:
> > > >
> > > > > Hi Kassie,
> > > > > I'm back from a holiday, hence the lack of follow up.....
> > > > >
> > > > > I'm not sure that I have explained my question properly...reading your
> > > > > advice I think it is not what I need, so let my further ask as follows:
> > > > >
> > > > > In an existing spreadsheet I have cell names: name 1, name2, etc..there are
> > > > > 24 named cells.
> > > > > I now want to allocate these same names to many other (old) spreadsheets.
> > > > > Your most recent help advises to "Select the cell i want to name, type in the
> > > > > name..." This requires that I type in the name into the 24 cells of each
> > > > > (old) spreadsheet, whereas what I'm hoping to be able to do is to copy and
> > > > > paste the cell name. (If I must type in the cell name, then I may as well
> > > > > just use "Insert/Name/Define")
> > > > >
> > > > > Or have I misunderstood your advice?
> > > > >
> > > > > Allan
> > > > > "Kassie" wrote:
> > > > >
> > > > > > Use the following to start with:
> > > > > >
> > > > > > Sub CopyName()
> > > > > > Dim rngName As Variant
> > > > > > rngName = InputBox("Insert the Range Name you want to copy")
> > > > > > With ActiveCell
> > > > > > ActiveWorkbook.Names.Add Name:=rngName, RefersTo:=ActiveCell
> > > > > > End With
> > > > > > End Sub
> > > > > >
> > > > > > Select the cell you want to name, type in the name, and it will create the
> > > > > > range in the active cell
> > > > > > --
> > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > >
> > > > > >
> > > > > > "Al" wrote:
> > > > > >
> > > > > > > Just wondering whether my macro can be written along the following lines:
> > > > > > >
> > > > > > > A simple macro for Insert/Name/Define uses the current cell, i.e.
> > > > > > > ActiveWorkbook.Names.Add Name:="cellname1", RefersToR1C1:="=Sheet1!R1C1"
> > > > > > >
> > > > > > > If I go to another cell, say R17C11, and run this macro, then it renames
> > > > > > > cell R1C1 as "cellname1". Is there a way of having the macro use the "current
> > > > > > > cell", something along the lines of....
> > > > > > > ActiveWorkbook.Names.Add Name:="cellname1",
> > > > > > > RefersTocurrentcell:="=Sheet1!currentcell"
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Al" wrote:
> > > > > > >
> > > > > > > > Thanks Kassie....I'll see what I can do with your help!
> > > > > > > >
> > > > > > > > "Kassie" wrote:
> > > > > > > >
> > > > > > > > > Hi Al
> > > > > > > > >
> > > > > > > > > You could use a macro to name the ranges in the other notebooks, but what
> > > > > > > > > worries me is that you say they are not all in the same place? You will have
> > > > > > > > > to pass some criteria to the macro, so that the range names are created with
> > > > > > > > > the correct ranges!
> > > > > > > > >
> > > > > > > > > If the macro must ask for ranges everytime it tries to create a range name,
> > > > > > > > > you may as well do it yourself, without having to go the the effort of
> > > > > > > > > writing code to do it!
> > > > > > > > >
> > > > > > > > > If these range name referred to similar ranges in the various books, it
> > > > > > > > > would be a breeze of course, to use a macro.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Al" wrote:
> > > > > > > > >
> > > > > > > > > > By "cell names" I mean that I have defined certain cell names using
> > > > > > > > > > Insert/Name/Define. Say A10 is named "total revenue" in workbook "ABC".
> > > > > > > > > >
> > > > > > > > > > I now want to allocate the same cell name to cell A30 in workbook "XYZ".
> > > > > > > > > >
> > > > > > > > > > Short of renaming the cell in the 2nd workbook - which I'm trying to avoid
> > > > > > > > > > becuase I have many cells that are named in "ABC", and dozens of other
> > > > > > > > > > workbooks need the same cell names - I'm hoping that I can copy in some way a
> > > > > > > > > > name from one book to another. (note the named cells are not necessarily in
> > > > > > > > > > the same place in spreadsheet.)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Kassie" wrote:
> > > > > > > > > >
> > > > > > > > > > > Hi Al
> > > > > > > > > > >
> > > > > > > > > > > Not sure what you mean with "cell names". If you are referring to the
> > > > > > > > > > > contents of the cell, you can either select the cell, press <Ctrl><C>, go
> > > > > > > > > > > where you want to paste, and press <Ctrl><V>, or you can go to where you want
> > > > > > > > > > > it on the other sheet, type in = click on the source sheet, click on the
> > > > > > > > > > > particular cell, and hit <Enter>
> > > > > > > > > > > --
> > > > > > > > > > > [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Al" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > How can I copy cell names from one spreadsheet to another?