Excel Macros for Worksheet tabs

[coachme]

I have a client that runs a transportation company. They create a printed reservations manual and they use excel to do it. They use the worksheet tabs as days and dates of the week. Is there a macro or something that will automatically put the date on the page so they won't have to type it in? You can't do this on the tabs manually? You can't simply use the header/footer function because when you put in the date selector it only changes when the date actually changes and they want to print the books up to a year in advance (that's how far in advance they accept reservations).

 

[lenze]

Range("$A$1").Value = ActiveSheet.Name will place the name of a sheet in A1. You will need to decide how you want to trigger the macro. You may need to loop or use an Event procedure depending on how they wish to do it.

HTH
This message was edited by lenze on 2003-01-03 17:27

 

[Bariloche]

coachme,

Try this:

<pre>
Sub CreateOrderSheets()
Application.ScreenUpdating = False
StartDate = 37621

For i = 1 To 365
DoW = Application.WorksheetFunction.WeekDay(StartDate + i, 2)

If DoW <> 6 And DoW <> 7 Then
ActiveSheet.Name = Format(StartDate + i, "d-mmm-yy")
PreviousSheet = ActiveSheet.Name
ActiveSheet.Copy After:=Sheets(PreviousSheet)
End If
Next i

Application.ScreenUpdating = True
End Sub
</pre>

I've assumed the following:

1) Your client has a workbook containing one sheet that they use as a template. This workbook needs to be the open, active workbook when this routine is run.

2) They don't take orders on weekends.


Give it a try and see if it does what you want.

Also, I've hard coded in the the serial number for 12/31/02. That will either need to be changed for next year or an input box needs to be added to prompt the user to put in the "StartDate".


have fun

 

[coachme]

Thanks, this is great!