copy and change date

[joeny0706]

I have an excel file that I receive daily. I have a column with a date that I need to be changed to the day before and then that column copied to another. I have been looking how but no luck yet.
I already have two VBA modules I run on it to change theproducts and names to match my format. But now I need a module to change dateand copy column so I don’t have to manually do it each time.

Anyone want to help or point me in the direction to complete this task.

I have attached two files. One is the before and the second it what I am tryingto convert it to

The date in column E is 11/19/2019. I need that changedto 11/18/2019. I will always need to changed to the day before the one that islisted. If it says 11/25/2019 I will need it changed to 11/24/2019. I then need column E after the date is changed copied to column I.


Is that something that is possible to do. Right now I just do a replace and copy and paste. If I could have a VBA code that does it would be great and save me a lot of time.

Thanks All

[SamT]

Sub RedateAndCopy()
Dim Cel As Range
Dim NewDate As Date
Dim DateRange As Range

With Sheets("Input")
Set Cel = .Range("E2")
Set DateRange = Range(Cel, Cel.End(xlDown))
NewDate = Cel - 1
DateRange = NewDate
DateRange.Copy .Range("I2")
End With
End Sub

[joeny0706]

That works perfect

Thanks for the help

[SamT]

:thumb

[snb]

Or


Sub M_snb()
With Sheet1.UsedRange
.Columns(5).Replace Sheet1.Cells(2, 5), Sheet1.Cells(2, 5) - 1
.Resize(, 9).Columns(9) = .Columns(5).Value
End With
End Sub