Formatting Consecutive Worksheets

  • Thread starter Thread starter Danno
  • Start date Start date
D

Danno

Hello All,

I'll try to explain my problem as clearly as possible, so please bear with
me.

I'm creating a pilot's log book using Excel. It looks just like the real
life logbook and therefore will have perhaps a hundred sheets, just like the
real log book has a hundred pages.

I have the first few sheets working well in that, as the pilot enters his
data, totals are calculated in the appropriate boxes at the bottom of the
sheet.

My problem is about creating new blank sheets that are ready to accept data.
Some of the data in Sheet 6 for example is dependant upon totals created in
Sheet 5. So I select Sheet 5 and make a copy of it, which I call Sheet 6.
The formula we would see in one of the cells on Sheet 5 would read
=SUM(G32:P32)+Sheet4!E32. But in the newly created copy of Sheet 5 (now
called Sheet 6) the cell in question still relates to a total collected from
Sheet 4, when I now want it to relate to that particular cell but from Sheet
5. Can you please tell me how to create a new Sheet without having to
manually check and change this cell? It's just that I have perhaps a
hundred sheets to create.

So far, I have had to create these new Sheets one at a time. Is there a way
to create the next 100 Sheets fairly quickly and easily while at the same
time carrying forward the formatting required to progressively bring forward
these totals?

Thanks in advance for any help.

Danno
 
Danno

This *may* help *if* the sheets are "Sheet1", "Sheet2", etc.

This formula in A1 of Sheet1,

="Sheet"&MID(CELL("filename",A1),FIND("Sheet",CELL("filename",A1))+5,LEN(CEL
L("filename",A1))-FIND("Sheet",CELL("filename",A1)))-1&"!B5"

should give you "Sheet0!B5".

If you then use it in A1 of Sheet2, you'll get "Sheet1!B5". If you then
enter e.g. 100 in B5 of Sheet1 and tweak A1 of Sheet2 to

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("Sheet",CELL("filename",A1))+
5,LEN(CELL("filename",A1))-FIND("Sheet",CELL("filename",A1)))-1&"!B5")

you should get 100.

If this works, group all the sheets from Sheet2 (with Sheet2 active, scroll
to the last sheet tab, then click on it while holding SHIFT). Click in the
formula bar, then press ENTER. This should replicate the formula in A1 of
all grouped sheets. From there, every sheet should return whatever's in B5
of the previous sheet.

Rgds,
Andy
 
Hi Danno,

I held back because I was sure that there must be a more elegant solution,
but in the absence of other solutions why not do the whole thing with a
macro?

The following macro will add as many sheets as you want - just make sure
that the last sheet has not yet been filled in.

Option Explicit
Sub MakeNewSheets()
Dim SheetsAdd As Long
Dim LastSheet As Long
Dim CountIt As Long

SheetsAdd = CInt(InputBox("How many Sheets do you want to add?"))

LastSheet = Sheets.Count
For CountIt = LastSheet To LastSheet + SheetsAdd - 1
Sheets(CountIt).Copy After:=Sheets(CountIt)
Sheets(CountIt + 1).Name = "Sheet" & CountIt + 1
Sheets(CountIt + 1).Range("E32").Formula _
= "=SUM(G32:P32)+" & Sheets(CountIt).Name & "!E32"
Next CountIt
End Sub

HTH

Sandy
 
Back
Top