Copying Worksheets with Named Ranges

  • Thread starter Thread starter Anthony Cravero
  • Start date Start date
A

Anthony Cravero

Hi All,

I'm mainly an Access person, but I had to do a quickie spreadsheet (Excel
97) to assist in grading papers. I ended up making 1 sheet per student, so
there will be 1 workbook per class, and used named ranges on each sheet.
When I copy that 1 sheet so I have 1 for each student, will I run into
issues with the named ranges? It seems to be working, but each worksheet
has the same range names. Do I need to change the ranges page by page, cell
by cell, or is there a way (VBA or otherwise) to simply update all ranges 1
worksheet at a time, or all at once?

Thanks,
Anthony
 
Anthony Cravero said:
Hi All,

I'm mainly an Access person, but I had to do a quickie spreadsheet (Excel
97) to assist in grading papers. I ended up making 1 sheet per student, so
there will be 1 workbook per class, and used named ranges on each sheet.
When I copy that 1 sheet so I have 1 for each student, will I run into
issues with the named ranges? It seems to be working, but each worksheet
has the same range names. Do I need to change the ranges page by page, cell
by cell, or is there a way (VBA or otherwise) to simply update all ranges 1
worksheet at a time, or all at once?

Thanks,
Anthony
Click to expand...

Suppose you have a worksheet (Sheet1) in which A1 is defined as "range1".
When you copy Sheet1 and make a new worksheet (Sheet2), this will contain a
name "range1" that refers to its own A1. So, the formula
=range1
will mean a different thing on Sheet1 from what it means on Sheet2. On
Sheet1 it is equivalent to
=Sheet1!A1
whilst on Sheet2 it means
=Sheet2!A1.

If your worksheets are all independent of each other, this will not cause a
problem. By independent, I mean that you only use the names within each
worksheet. The confusion would come if you tried to use names between
worksheets, for example to make a summary sheet. If you are not going to do
this, leave well alone!
 
Paul--

That's just what I needed to know! Thank you for your quick response!

-Anthony
 
Back
Top