Results 1 to 4 of 4

Excel Macros for Worksheet tabs

This is a discussion on Excel Macros for Worksheet tabs within the Excel Questions forums, part of the Question Forums category; I have a client that runs a transportation company. They create a printed reservations manual and they use excel to ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    2

    Default

    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).

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    143

    Default

    coachme,

    Try this:


    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


    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

  4. #4
    New Member
    Join Date
    Jan 2003
    Posts
    2

    Default

    Thanks, this is great!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com