Tracking many ledgers in Excel?

[geigersc]

I am a teacher that has to keep track of 150 student ledgers. Is there a way
to get Excel to do the following? (2007):

1) I cannot seem to make all the headers/footers the same from worksheet to
worksheet in the same book. Due to turnover (about 30+ students a year), it
is not a task wirth undertaking to "reheader" every 150 ledgers.
2) Find worksheets once I label them with student's names (using the "Find"
search). Trying to scroll with the little arrows is not easy with that many
worksheets.

If there is a different MS program that will acomplish this better, please
let me know. There are programs made for this, but all costing several
hundred dollars. I'm hoping that Excel can do what I need it to do. Please
help!

 

[Otto Moehrbach]

Looking at your second question first, try this: Right-click on those
little scrolling arrows. Any of them. This displays a list of all the
sheets in the workbook. If the last item in the list is "More", click on
that. That will display a scrollable list of all the sheet names. Click on
a sheet name and that sheet will be selected.
Now your first question: I don't understand your question. Why can't you
make the headers the same? What are the headers? Provide a bit more detail
about what this "task" entails. It sounds like it's something you can do
with VBA, but more details from you are needed to understand what the
problem is. HTH Otto

 

[geigersc]

This helped a lot. Some of what I don't know is lack of experience, however
I'm getting better. I do have some other quesitons:

1) Can I alphabatize my worksheets?
2) Can I search for a worksheet through "Find"? (not a cell)

 

[Mike Rogers]

geigersc

Take a look here for your first question:

http://www.cpearson.com/excel/sortws.htm

This will give you help on building a table of contents and therefore
eliminate the need to find:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm


Hope this helps

Mike Rogers

 

[Stan Brown]

Sat, 23 Feb 2008 05:15:26 -0800 from geigersc

I am a teacher that has to keep track of 150 student ledgers. Is there a way
to get Excel to do the following? (2007):

1) I cannot seem to make all the headers/footers the same from worksheet to
worksheet in the same book. Due to turnover (about 30+ students a year), it
is not a task wirth undertaking to "reheader" every 150 ledgers.

I have a macro in my workbook that applies the desired headers to
every worksheet when I open the workbook. You can make whatever
changes to make the headers come out as you like. Footers can be done
in the same way, and within the same loop.

Sub Workbook_Open()
Dim ws As Worksheet
Dim thisYear As Integer
thisYear = Evaluate("Year(FirstClass)")
For Each ws In Worksheets
ws.PageSetup.LeftHeader = Worksheets("Rules").Range
("course").Value _
& "/" & Worksheets("Rules").Range("section").Value
ws.PageSetup.RightHeader = Worksheets("Rules").Range
("semester").Value & " " & thisYear
Next
ActiveWorkbook.Saved = True
End Sub

The last line of the macro ensures that I don't get the "do you want
to save?" message if I open the workbook, look at it, and close it
without actually changing anything.



--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top posting such a bad thing?

 

[geigersc]

THANK YOU! Now for a really stupid question:

Where do I put his coding?

I'm to new to this to understand that much. 2007 has me a little thrown as
well, however I'm trying to adapt.

 

[Mike Rogers]

geigersc

One of many places on the web to learn about macros.

http://web.archive.org/web/20031204...cid=/support/excel/content/vba101/default.asp


Mike Rogers