Serial Date in Multiple Worksheet

[John]

Hi, I need to create a workbook with the same Multiple Worksheet (TAB) form
on it. This form will be used for each day of the month. The problem is I
want to be able to enter the date on the first TAB and has the date replica
to each worksheet add 1 day to each sheet.

Example:

The first sheet - Monday, December 26, 2005; the second - Tuesday, December
27, 2005; the third - Wednesday, December 28, 2005; and so on.

Thanks in advanced.

 

[Max]

Try running the sub below (Sub is by Dave Peterson, and was plucked from a
post by Gord Dibben in .newusers)

Here's how to set it up ..

In a new book,
Name 2 empty sheets as: List, Template

In List, enter in A1 the first date, say: 26-Dec-2005
Copy A1 down to say, A10, to fill the range till 04-Jan-2006

In Template, we could prepare a sample "template" table with col headers,
sample data, format to taste, etc (or just leave the sheet blank for now)

The sub will copy the sheet named as: Template
and create & name the new sheets
according to the list in col A in List

Steps
--------
Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the code window (whitespace) on the right

'-------begin vba-----
Sub CreateNameSheets()
' by Dave Peterson
' (slightly revised by Max to format sheetnames <g>)
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = Format(myCell.Value, "dddd, mmmm dd, yyyy")
If Err.Number <> 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub
'-------endvba------

Press Alt+Q to get back to Excel

In Excel, press Alt+F8 (brings up the Macro dialog)
Select "CreateNameSheets" > click "Run"
(or just double-click directly on "CreateNameSheets")

The sub will create 10 new sheets to the right,
naming these as desired:
Monday, December 26, 2005
Tuesday, December 27, 2005
....
Wednesday, January 04, 2006

 

[John]

Thanks for the quick response. I really like that scrip, but I guest I wasn't
clear enough.

What I want to do is in a "cell" auto fill the date in each sheet.

Example: sheet1 - cell A4 - Monday, December 26, 2005; sheet2 - cell A4 -
Tuesday, December 27, 2005; sheet3 - cell A4 - Wednesday, December 28, 2005

Each sheet will be exactly the same but the date will change. The TAB name
mean nothing.

Thanks

 

[Max]

Sorry for the mis-interp, John !
I don't have further ideas to offer here

Do hang around awhile for insights from others

 

[John]

Thanks, I will

 

[Gord Dibben]

John

Sub Date_Increment()
''increment a date in A4 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2005, 12, 27)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A4")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr
End Sub


Gord Dibben Excel MVP

Thanks, I will

 

[John]

Thanks for the script. But how do I use it?

John

Sub Date_Increment()
''increment a date in A4 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2005, 12, 27)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A4")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr
End Sub


Gord Dibben Excel MVP

Thanks, I will

 

[John]

Thanks I figure it out.

Thanks for the script. But how do I use it?

John

Sub Date_Increment()
''increment a date in A4 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2005, 12, 27)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A4")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr
End Sub


Gord Dibben Excel MVP

 

[John]

Hi, the script works great. I do have one problem.

The line "myDate = DateSerial(2005, 12, 27)". The way the script is written
I would need to edit the script to get the correct dates.

What I am hoping to do is enter the date from the cell in the first
worksheet and then run the macro replic to the other sheets.

Thanks

John

Sub Date_Increment()
''increment a date in A4 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2005, 12, 27)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A4")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr
End Sub


Gord Dibben Excel MVP

Thanks, I will

 

[Dave Peterson]

You can change this line to point at the cell you want:
From:
myDate = DateSerial(2005, 12, 27)
to:
myDate = worksheets("sheet99").range("a1").value

Change the sheet name and the cell address to what you need.

Hi, the script works great. I do have one problem.

The line "myDate = DateSerial(2005, 12, 27)". The way the script is written
I would need to edit the script to get the correct dates.

What I am hoping to do is enter the date from the cell in the first
worksheet and then run the macro replic to the other sheets.

Thanks

John

Sub Date_Increment()
''increment a date in A4 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2005, 12, 27)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A4")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr
End Sub


Gord Dibben Excel MVP

 

[John]

Thanks, it all works fine now.

You can change this line to point at the cell you want:
From:
myDate = DateSerial(2005, 12, 27)
to:
myDate = worksheets("sheet99").range("a1").value

Change the sheet name and the cell address to what you need.