A lot of reasons come to mind why you shouldn't use such a formula, i
it was possible to write one - the first and most critical being i
someone (even you) accidently reordered the worksheets by clicking an
draging one to a new location, all your calculations would suddenly b
returning bad values - AND you would have almost no way of knowing i
had been done.
Excel does keep a collection of worksheets (that's why you can us
something like Worksheets(1).range("A1").value and get a value back) an
there are worksheet properties .Next and .Previous which return
worksheet object. In VBA
strNextWorksheetName = Activesheet.Next.Name
should give you the name of the next workbook - but again, if anyon
changes the order of the worksheets, you're dead.
It sounds like you will make a copy of the worksheet you are going t
insert and then insert it into the right position. Let's call the shee
you are pasting in My_Copy and let's say the formulas on the My_Cop
sheet you are going to paste in are like
=SUM('Base Sheet'!C7:C14)
When you paste My_Copy in front of the worksheet named Real_Sheet al
you have to do is a Find and Replace in the My_Copy worksheet to chang
all the worksheet references.
That is, do a find on
'Base Sheet'!
and replace all occurances with
'Real_Sheet'!
That changes all your formulas in My_Copy to point at the values i
Real_Sheet - and it's a static link in the sense that it doesn't matte
what the order of the sheets is.
(Actually, the single quotes in 'Real_Sheet' aren't necessary becaus
there aren't any blanks in the sheet name, but Excel won't mind - an
it's a good habit to get into, just in case you forget that they have t
be there if the sheet name DOES have an embedded blank)
Hope this helps, even though it isn't what you really wanted... :Bg
