Excel 2003 - Need new sheet tabs

Microsoft Excel 2003 (full product)
December 31, 2009 at 07:44:21
Specs: Windows XP
I need to create A list of Stops for Appointments for the entire year. I need a sheet for each day Monday through Friday. Just need to know how to create a tab for each day so If asked I can go to it and see if I can schedule it .

See More: Excel 2003 - Need new sheet tabs

Report •

December 31, 2009 at 08:11:00
Right click a sheet tab and choose Insert, then choose Worksheet. This will add a new Worksheet tab to your workbook.

If you Shift-Click to select more than one worksheet and then use Right-Click...Insert...Worksheet, Excel will insert the same number of sheets as you have selected.

Once you have your new tabs, Right-Click them one by one and choose Rename. Once the text is highlighted, you can name the sheets based on the days of the week by typing in the names.


Report •

December 31, 2009 at 08:17:39
So I have to insert a tab for each day which is what 200 times? I thought there was a simple command or formula to do this ? am I wrong?

Report •

December 31, 2009 at 08:41:58
Using the Shift-Click to select more than one worksheet and then use Right-Click...Insert...Worksheet you can do it in about eight inserts:

You start out with the original three tabs then, after the insert you now have six, copy the six and you now have 12, 48, 96, 192 then copy only 8 tabs and your done.

But wouldn't a calendar type program work just as well as two hundred tabs?



Report •

Related Solutions

December 31, 2009 at 10:18:04
In your OP your said:

I need a sheet for each day Monday through Friday

As written, that's a grand total of 5 tabs.

If you need many more tabs than that, it could be automated, but what are you going to name each tab? Will each one have a date as a name?

Report •

December 31, 2009 at 10:54:29
This code will add a sheet for every Monday - Friday in 2010.

It will write data into Sheet1 Columns A & B, so make sure you add a new sheet (or at least columns) so that you don't lose any data.

After the new sheets are created, you can delete any extra sheets from the beginning of the workbook.

As always, this code should be tested in a backup copy of your workbook.

P.S. This could all be done without writing data to a worksheet, but it worked as visual check, so I just left it in. For a one time workbook creation, it's fine.

Sub SheetsByDate()
Dim dateRow, myDate, myDay, newSht
'Initialize dateRow Variable
dateRow = 1
'Loop to put Dates and Days in Columns A & B
'(40179 = 1/1/2010)
 For myDate = 40179 To 40179 + 365
  myDay = WeekdayName(Weekday(myDate))
'Skip Weekends
   If myDay = "Sunday" Or myDay = "Saturday" _
              Then GoTo WeekEndDay
'Increment dateRow
    dateRow = dateRow + 1
'Populate cells (the weekday names aren't used
'other than for a "visual" check
     Sheets(1).Cells(dateRow, 1) = myDate
     Sheets(1).Cells(dateRow, 2) = myDay
'Loop to Add sheets and Rename them
  For newSht = 2 To dateRow
'Add sheets
   Sheets.Add after:=Sheets(Sheets.Count)
'Rename them based on Dates in Column A
'Format = mm-dd-yyyy
    ActiveSheet.Name = _
      Month(Sheets(1).Cells(newSht, 1)) & _
      "-" & Day(Sheets(1).Cells(newSht, 1)) & _
      "-" & Year(Sheets(1).Cells(newSht, 1))
End Sub

Report •

December 31, 2009 at 12:10:49

The following code creates a tab for every weekday in the year.
Each tab is labeled with day and date, e.g., Mon_04-Jan

Open a new workbook. Note its name
Hit Alt+f11 (Alt key and function key #11).
This opens the visual basic window.
Look for the new workbook in the pane on the left.
Double click ThisWorkbook under its name.
Paste the following code into the pane on the right.

Sub AddSheets()
With ActiveWorkbook
    For intDy = 40179 To 40179 + 364
        .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
        .Worksheets(.Worksheets.Count).Name = Format(intDy, "ddd_dd-mmm")
        If Weekday(intDy, vbSunday) = 6 Then
            intDy = intDy + 2
        End If
    Next intDy
End With
End Sub

Click anywhere inside the code Hit f5
All the new tabs will be created and named
Close the window and return to Excel

If this works OK for you make a backup of your main Excel workbook.
Copy the code into its ThisWorkbook (Alt+f11)
Click inside the Code and hit f5 to run the code.


Report •

December 31, 2009 at 17:47:38
Thanks this is what I needed.

Report •

Ask Question