Need help on a calender type spreadsheet

[kayak99]

Hope I explain this correctly. I need to make (two sided) tabbed spreadsheets
available for individuals to use for each day of the year.

I'm okay building the speadsheets themselves but I want to date each one,
front and back for the current date without having to type that in daily.

Isn't there a way I could do one or two each month, then drag the tabs out
to, say 31, for January, repeat for Februuary, etc?

Or, is there a way to have one spreadsheet and each time it's opened it has
the current (computer) date on it?

I'm somewhat formula deficient so please be real specific

Thanks

 

[Pete_UK]

If you have this formula in a cell:

=TODAY()

then everytime you open the file it will show the current date in that
cell automatically.

So, you could derive your dates from this.

However, it would mean that the dates would change every (new) day
that you opened the file.

It might be better to put the start date in a cell (eg put 01/01/2009
in A1), then in A2 you can have this formula:

=A1+1

which should show 2nd January, and you can copy the simple formula
down to row 31 to get all the dates in January.

Then for a new month, all you need is to change the starting date in
A1 and the other cells will change automatically.

Hope this helps.

Pete

 

[Pete_UK]

I didn't realise you wanted a sheet for each day of the month. You can
copy sheets quite easily by holding the CTRL key down and then just
dragging the sheet tab along the bottom (a small arrow will indicate
where the tab will be positioned). You can do this 30 times, and then
manually change the name of each tab. If you name them 1st, 2nd, 3rd
etc, then once you have done it for January, you can just use File |
Save As to save the files with different names for each month, and
then for 30-day months (and for February) you can delete the sheet(s)
that you don't need.

Hope this helps.

Pete

 

[kayak99]

Pete,

That does give a months worth of spreadsheets but it appears I have to go
back and manually rename the tabs as well as change the dates on each sheet.

It was those two manual changes that I was trying to avoid beacuse I want to
chart an entire year. That's a lot of renaming - two each day (tab date and
cell date).

Just to review if I didn't state my needs correctly, same spreadsheet,
different dates, one for each day of the year.

Maybe there is no way to accomplish this other than your manual suggestion.

Thanks for your help.

 

[Pete_UK]

That's why I suggested having a file for each month (rather than one
file for the whole year - do you know what 365 sheets in one file will
look like?)

You can group sheets together and then any changes you make to one can
be reflected in all the grouped sheets. For example, in the first
(monthly) file you create you could group sheets 2nd to 31st together
and in cell A1 you can enter this formula:

=IF(1st!A1="","",1st!A1)

Then ungroup the sheets by clicking on the tab for 1st, and you can
enter January in cell A1. Now all those other sheets will show January
in cell A1. When you come to save this file as February, all you need
to do is change the entry in A1 of the 1st sheet to February, then all
those other sheets will show that month.

Hope this helps.

Pete

 

[Bernie Deitrick]

Kayak,

You could use a macro to copy and rename the sheets: this code is based on the current month, but
you could change it to do any month desired.

Sub TryNow()
Dim i As Integer
Dim iDays As Integer

iDays = Day(DateSerial(Year(Date), Month(Date) + 1, 0)) - 1

For i = 1 To iDays - 1
Worksheets(1).Copy Worksheets(1)
Next i

For i = 1 To iDays
Worksheets(i).Name = _
Format(DateSerial(Year(Date), Month(Date), i), "mmm dd")
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Gord Dibben]

Are you saying that you want 365 worksheets in a workbook?

One worksheet for each day of the year?

Or one workbook with worksheets for each day of one month?

And please explain what you mean by "two sided tabbed spreadsheets"


Gord Dibben MS Excel MVP

 

[kayak99]

I need a years worth of dated spreadsheets and it will printed out on two
sides, front and back with the date on each side. Its actually a time sheet
of sorts and typically only used Mon -Fri.

The twelve month file mentioned above seems best. We have an older set of
these but someone had taken the time to actually type the date into each of
365 pages front and back. I'm wanting to use the same sheets but without
having to type a date into each page.

Haven't worked with macros and my experience with Excel was limited to more
simple spreadsheets.

Still muddling through but haven't landed on the perfect solution yet.

Thanks for the help though!

 

[Gord Dibben]

Bernie's code to copy Sheet1 and rename to mm dd would give you a workbook
with just one month's sheets.

I think that would be best...........12 workbooks......one for each month,
would be much less unwieldy than 365 sheets in one workbook.

Or you could have 12 sheets, one for each month, in a single workbook.

We could work up code for that if you should choose.

As far as printing on two sides, that would be another set of code to get a
date on each side and would depend upon your printer supporting double-sided
printing.


Gord

 

[kayak99]

Gord, sorry I was tied up on another project and had to stop work with this
one. Yes, 12 workbooks, one for each month would work best. What code would
you use? I'm not very good with macros so please be specific. Thanks

 

[Gord Dibben]

I'm still not sure what you want for an end result.

Easy enough to add a month's worth of date-named sheets to a new workbook.

Like 01 Jan 2009 through 31 Jan 2009

You can even add sheets just for weekdays...omitting weekends for that month

It is the "double-sided with dates front and back" that confuses me.

Let's say you have a workbook with 31 sheets for January, 2009.

How would you want those 31 sheets printed out?

Would each sheet be one printed page or more?

Which pages would be printed "front and back"?

Would the Date in a header or footer suffice?


Gord