Excel or VB help required

  chris9277 14:59 14 Apr 2009
Locked

Hi there,

In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:

Sat 26/04/2008 gone off sick
Fri 25/04/2008
Thu 24/04/2008
Wed 23/04/2008
Tue 22/04/2008
Mon 21/04/2008
Sun 20/04/2008
Sat 19/04/2008
Fri 18/04/2008 days holiday
Thu 17/04/2008
Wed 16/04/2008
Tue 15/04/2008


I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.

Many thanks,

chris

  OTT_Buzzard 15:34 14 Apr 2009

Hmmm, you may have a problem with this.

=today() will always show the current date. So if you insert a new line(s) wiht the same formula you'll end up with all of your cells saying the same date.

So,

Insert the following code in to a new module in your VBA editor:

Sub InsDate()
Range("a1").Select
Selection.EntireRow.Insert
ActiveCell.Value = Date
End Sub

Assign a shortcut key to run the macro when you need to. Alternatively use this slightly modified version:

Sub InsDate()
Range("a1").Select
With ActiveCell
If .Value = Date Then
Exit Sub
End If
.Insert
.Offset(-1, 0).Value = Date
End With
End Sub

This will check the last entry in the date list and check to see if it todays date. If it is the procedure ends. If not it will insert a line and enter todays date.


If you want the macro to tun when the workbook opens, insert the folling code into the 'This Workbook' page:

Private Sub Workbook_Open()
Call InsDate
End Sub


This basically means that when the workbook opens it will run the code above.

  OTT_Buzzard 15:40 14 Apr 2009

Made a mistake on the second version of the code. It should say:

Sub InsDate()
Range("a1").Select
With ActiveCell
If .Value = Date Then
Exit Sub
End If
.EntireRow.Insert
.Offset(-1, 0).Value = Date
End With
End Sub

  chris9277 19:16 14 Apr 2009

Buzzard,

Firstly thank you very much for taking the time to create that VBA for me it's greatly appreciated, it's almost perfect. The only problem I have is it looks up the last date & inserts the current date. What I would like it to do if possible is insert the lines if it hasn't been opened for a couple of days. So lets say I have been away from my desk for the weekend I would lik it to also input sat, sun & mon so that thereis never a day missed. can this be done????

Many many thanks

Chris

  OTT_Buzzard 19:43 14 Apr 2009

Sub InsDate()
Dim LastDate As Date
Range("b5").Select

LastDate = ActiveCell.Value

Do
Select Case ActiveCell.Value

Case Is < Date
With ActiveCell
.EntireRow.Insert
.Value = LastDate + 1
LastDate = LastDate + 1
End With
Case Is = Date
Exit Sub
End Select

Loop Until LastDate = Date

End Sub

  VoG II 19:45 14 Apr 2009

Cross-posted here click here

  OTT_Buzzard 19:53 14 Apr 2009

sorry, another mistake in the last code. Should be:

Sub InsDate()
Application.ScreenUpdating = False
Dim LastDate As Date
Range("b5").Select

LastDate = ActiveCell.Value

Do
Select Case ActiveCell.Value

Case Is < Date
With ActiveCell
.EntireRow.Insert
.Offset(-1, 0).Value = LastDate + 1
LastDate = LastDate + 1
End With
Case Is = Date
Exit Sub
End Select

Loop Until LastDate = Date

End Sub

  chris9277 20:01 14 Apr 2009

Buzzard ***ARE A GENIUS****

I have been searching high & low for this.......Fantastic.

Many MANY thanks for this.

  OTT_Buzzard 20:11 14 Apr 2009

in more than one forum.
It's no problem to work out the code, but please remember

1. for every forum you post the same question in, you can have hundreds, if not thousands, of people reading it and possibly working on it. Please only post the same question to another site if you have received no help. That will save time for everybody else.

2. IF YOU COPY / PASTE MY WORK TO ANOTHER FORUM REFERENCE WHERE YOU GOT IT FROM.

  chris9277 14:53 15 Apr 2009

Ok thank you buzzard I will reference any work I paste that's the least I can do. Using forums to answer my questions is quite new to me but I am learning fast.

Just one question Buzzard? I have pasted in the VBA you sent with regards to the sheet automatically updating itself upon opening it but it fails. That wonderful VBA you did for me is working wonders & it is being used across all my tabs which there are over 50 of in the same workbook. Would it be failing due to the amount of sheets??

Many thanks

Chris

  OTT_Buzzard 15:13 15 Apr 2009

You can have up to 256 sheets in Excel (*prior to 2007) and a lot more than that in 2007.

I'm not sure that i understand what the problem is?
Do you need the macro to run on more than one worksheet when the file is opened?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

30 UK artists and their beautiful posters for WCRS' Pride in London campaign

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?