Fill cell column with current date

[Bluwara]

Morning All,

I need your help on my query. This is my query.

I have 3 spreadsheets in a workbook which the first spreadsheet is the data entry spreadsheet and the other is the quotation form to fill automatically from the details from the first spreadsheet. The 3rd spreadsheet is my register.

The register must also auto complete when when the data entry spreadsheet is filled. So every time when the data entry spreadsheet is filled, the next row in the register is filled as well.

My issue is on the date column of my register where I am finding it difficult to fill in the current date automatically when the data entry spreadsheet is filled for each row in the register.

Hope this is clear.

Thanks in advance...

Bluwara

 

[Bluwara]

If you enter say data in Cell C5 in Data Entry, you can do a formula in A2 in the Register as "=DataEntry!C5" to place the data in C5 to A2 in the Register.

But bcoz I am using the same spreadsheet for all my new entries, I am unable to use the earlier mentioned formula coz it won't work as required.

So I want help when I enter data in C5, A2 is filled and when C5 data is entered again, A3 is now filled as A2 was filled previously and so on.

Bluwara

 

[MARK858]

Code to go in the worksheet module of sheet Data Entry.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        Target.Copy Sheets("Register").Cells(Rows.Count, "A").End(xlUp)(2)
    End If
End Sub

 

[Bluwara]

Morning Mark858,

Your code works perfectly....

Thanks

Bluwara

 

[Bluwara]

Morning Mark858,

The formula works as requested but it is copying the details with all its settings and pasting it on the register. How can I amend to show the value only with the settings details.

Bluwara

 

[MARK858]

First of all it is not a formula, it is VBA code. Anyway try the code below...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        Sheets("Register").Cells(Rows.Count, "A").End(xlUp)(2).Value = Target.Value
    End If
End Sub

 

[Bluwara]

Hi Mark858,

This works fine.

Bluwara