Consulting

Results 1 to 5 of 5

Thread: copy and change date

  1. #1

    copy and change date

    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
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    That works perfect

    Thanks for the help

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •