I have a worksheet in excel that I would like to have automatically add
a copy of itself to the workbook on Mondays or every 7 days from a
specified date.

I also want this sheet to automatically add the the dates of the week
to cells 'g8', 'k8','o8','s8','w8'. So that if Mon is 05-05-05 then it
will show in cell g8 and Tuesday would show 05-06-05 and so on.

Is this possible and if so what would the code be.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=439578

One way (put in the ThisWorkbook code module):

Private Sub Workbook_Open()
Dim dNewDate As Date
With Worksheets
With .Item(.Count).Range("G8")
If IsDate(.Value) Then _
If Date - .Value < 7 Then Exit Sub
dNewDate = .Value + 7
End With
With .Add(After:=.Item(.Count)).Range("G8")
.Value = dNewDate
.NumberFormat = "mm-dd-yy"
.Resize(1, 4).AutoFill _
Destination:=.Resize(1, 17), _
Type:=xlFillDefault
End With
End With
End Sub




In article ,
oberon.black
wrote:

I have a worksheet in excel that I would like to have automatically add
a copy of itself to the workbook on Mondays or every 7 days from a
specified date.

I also want this sheet to automatically add the the dates of the week
to cells 'g8', 'k8','o8','s8','w8'. So that if Mon is 05-05-05 then it
will show in cell g8 and Tuesday would show 05-06-05 and so on.

Is this possible and if so what would the code be.

My previous post added a new sheet. To copy the last sheet in the
workbook:


Private Sub Workbook_Open()
Dim dNewDate As Date
With Worksheets
With .Item(.Count)
With .Range("G8")
If IsDate(.Value) Then _
If Date - .Value < 7 Then Exit Sub
dNewDate = .Value + 7
End With
.Copy After:=Sheets(Sheets.Count)
With ActiveSheet.Range("G8")
.Value = dNewDate
.NumberFormat = "mm-dd-yy"
.Resize(1, 4).AutoFill _
Destination:=.Resize(1, 17), _
Type:=xlFillDefault
End With
End With
End With
End Sub




In article ,
JE McGimpsey wrote:

One way (put in the ThisWorkbook code module):

<snip

In article ,
oberon.black
wrote:

I have a worksheet in excel that I would like to have automatically add
a copy of itself to the workbook on Mondays or every 7 days from a
specified date.

I also want this sheet to automatically add the the dates of the week
to cells 'g8', 'k8','o8','s8','w8'. So that if Mon is 05-05-05 then it
will show in cell g8 and Tuesday would show 05-06-05 and so on.

Is this possible and if so what would the code be.

where is the 'this workbook code module'

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=43957

I don't need this code to run on all the worksheets in my workbook. I
only need it for one worksheet.

Plus when I put the code in nothing happened.

Please advise.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=439578

The code doesn't run on all worksheets. It checks the last worksheet to
see if the date in G8 is within 7 days. If so, it exits, if not, it
copies that sheet and puts the new date in the new sheet's G8.

Where did you "put the code in"? If you put it in the ThisWorkbook code
module, the event macro fires each time the workbook is opened.

In article ,
oberon.black
wrote:

I don't need this code to run on all the worksheets in my workbook. I
only need it for one worksheet.

Plus when I put the code in nothing happened.

Please advise.

See

http://www.mcgimpsey.com/excel/modules.html

In article ,
oberon.black
wrote:

where is the 'this workbook code module'?