Select a range of cells based on system date

[manojrf]

Hi,

I have a worksheet which has today's date in column D , if there is data in column A , by giving the IF formula and by the formula =today() . In columns A , B and C some other data are entered. Suppose I enter data from A1 to C10 , I would like to like to select the cells entered today , to be printed automatically by the help of a macro. If I enter some more data tomorrow from A11 to C15, I would like to print only A11 to C15 .

In this worksheet, when I save the file can the date be saved , so that when I open it some other day the saved portion ( date ) doesn't change ?

Can anyone help me ?

Thanks in advance,

Manuel Z G

 

[WinteE]

Hi Manuel,

Copy the following code into the object of the sheet were columns A:D are in.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:c1000")) Is Nothing Then
    Range("D" & Target.Row).Value = Date
End If

End Sub

Success

 

[manojrf]

Hi WinteE,

The one you have sent me ,works fine. Superb !!! .Thanks a lot. I had asked one more thing. Could you please help me on that ?

I have a worksheet which has today's date in column D . (The one you sent me works fine. ) In columns A , B and C some other data are entered. Suppose I enter data from A1 to C10 , I would like to like to select the cells entered today , to be printed automatically by the help of a macro button. To make it some more clear what I need is, when I click the print macro button, the cells entered today has to selected and printed automatically. If I enter some more data tomorrow from A11 to C15, I would like to print only A11 to C15 tomorrow.

Could you help me please ?

Regards, Manuel

 

[WinteE]

Hi Manuel,

Add the code below to the object of the sheet :

Sub PrintToday()

i = Cells(Cells.Rows.Count, "B").End(xlUp).Row

For Each c In Range("D1:D" & i)
    If c.Value = Date Then
        CountVal = CountVal + 1
        If CountVal = 1 Then
            StartRow = c.Row
        Else
            EndRow = c.Row
        End If
    End If
Next c

Range("A" & StartRow & ":D" & EndRow).Select
Selection.PrintOut Copies:=1, Collate:=True

End Sub

Success

Erik

 

[manojrf]

Hi WinteE,

Thank you,thanks a lot for the one you sent me. It works superb !!!!! Thank you once again.

Regards, Manuel

 

[manojrf]

Hi WinteE,

What changes should I make , if the date column is changed to E from D ? I would like to print A : D .

Can you help me ?

Manuel

 

[WinteE]

Hi Manuel,

You'll have to make adjustments to both macro's, I've marked the changes bold.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:D1000")) Is Nothing Then
    Range("E" & Target.Row).Value = Date
End If

End Sub

Sub PrintToday()

i = Cells(Cells.Rows.Count, "B").End(xlUp).Row

For Each c In Range("E1:E" & i)
    If c.Value = Date Then
        CountVal = CountVal + 1
        If CountVal = 1 Then
            StartRow = c.Row
        Else
            EndRow = c.Row
        End If
    End If
Next c

Range("A" & StartRow & ":D" & EndRow).Select
Selection.PrintOut Copies:=1, Collate:=True

End Sub

Success,
Erik

 

[manojrf]

Hi WinteE,

I found out by myself the changes to be made. Sorry to bother you.

Anyway thank you so much.

Regards,

Manuel