Copy Value from Formula and paste on the next column/cell automatically

[Rubleon]

Hello team, can you please help me?

Thanks in advance.

I'm trying to imagine how to have a column that copy/paste the value from the previous cell, who gets the data through a formula. this through a VBA code.

So this is the formula used to populate column "B", which extracts the date from the "Entry Date" sheet.
=INDEX('Entry Date'!$A$2:$G$3,MATCH(B$1,'Entry Date'!$A$2:$A$3,0),MATCH($A2,'Entry Date'!$A$2:$G$2,0))
and what I want is to populate column "C" with the date value, each time I populate the next empty cell in column "B". this to properly use the date filter and be able to use that column for an "=IF AND AND" formula.

and I want this to happen, as it occurs on column F, every time I populate or change the status in column E.
attached you will find the example performed from the complete data.

Invoice No.	Shipment Date	Paste Date As ValueColumn		Status	Last Update
Data 1	09/11/2019			Must Reject	30/12/2019
Data 2	08/11/2019			Passed	30/12/2019
Data 3	01/11/2019
Data 4	01/11/2019			Passed	30/12/2019
Data 5	01/11/2019
Data 6	05/11/2019
Data 7

and this is the VBA code used to auto-populate the column F

Private Sub Worksheet_Change(ByVal Target As Range)
'Update DATES
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[JLGWhiz]

Kind of hard to determine for sure what you want, but this will put the value of column B into column C and put the current date into column F for any cell with a value in column E.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim c As Range
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Range("C2", Cells(Rows.Count, 2).End(xlUp).Offset(, 1)) = _
        Range("B2", Cells(Rows.Count, 2).End(xlUp)).Value
        For Each c In Range("E2", Cells(Rows.Count, 5).End(xlUp))
            If c <> "" Then
                c.Offset(, 1) = Date
            Else
                c.Offset(, 1) = ""
            End If
        Next
    End If
    Columns("C").NumberFormat = "dd/mm/yyyy"
Application.EnableEvents = True
End Sub

 

[Rubleon]

Thanks JLGWhiz,

apologies if i didn't described it as i should.

I get the values of the "Shipment Value", with an index match formula from another file... and somehow i cannot use that "Date Value", as described with the below example.

(tried to do it through the XL2BB range to BBCode, but didnt work)...
this formula goes into cell Q2
=IF(AND(M2<=P2,N2>=P2),O2)


I need help to create a VBA code, who will automatically copy/paste the Date value from the "Shipment Date" column, in the next cell each time I populate the value on the "shipment Value" cell.

 

[JLGWhiz]

Well, I thought that was part of what the code I wrote was doing. The problem is that the worksheet change will not work based on a change in column B because it is a calculation that causes the change and Excel ignores calculations as changes, so it does not trigger the Change Event. Conversely, if you use the Worksheet_Calculate to trigger the code, then it runs everytime a calculation occurs, whether it is in column B or not, so you would need to write the code to copy every item in column B to column C every time. Give this a try.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("B2", Cells(Rows.Count, 2).End(xlUp)).Copy
Range("C2").PasteSpecial xlPasteValues
Application.EnableEvents = True
End Sub