Forum Discussion

Kasey_Vincent's avatar
Kasey_Vincent
Copper Contributor
Jul 25, 2019
Solved

(Excel) Pulling date from another sheet, adding one week to it

I know I’m a little late to the party here, but Im trying to pull a date from a previous sheet and add it to the next sheet, while adding 7 days to the date. Is there a macro on how to do this? I’ve already found a Macro to pull information from a previous sheet, but I’m trying to use it to add days.. And I can’t. The cell I’m using is K1 and I’ve tried every combination I can think of with everything that’s listed here:

Added and modified this Macro:

Sub Add_Day_To_Date()
'Adds 1 to the active cell

  ActiveCell.Value = ActiveCell.Value + 1

End Sub

Tried using, modifying, and using variations of this formula:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7)

I've also tried the Paste special trick, but it doesn't seem to work when dealing with another sheet..


The macro I’m currently using is:

Function PrevSheet(RCell As Range)
Dim xIndex As Long
Application.Volatile
xIndex = RCell.Worksheet.Index
If xIndex > 1 Then _
PrevSheet = Worksheets(xIndex – 1).Range(RCell.Address)
End Function

What am I doing wrong? What would the formula or macro be that would give me the result I’m looking for?

  • Okay, Never mind. I figured it out. It turns out it was a really simple formula after adding that macro for the PrevSheet object. All I had to do was add +7 to the end of it. I was way overthinking it. Maybe by leaving this here it will help someone else. My formula in the K1 field looks like this now:

     

    =PrevSheet(K1)+7

     

    And the macro as I said earlier was:

    Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
    PrevSheet = Worksheets(xIndex – 1).Range(RCell.Address)
    End Function



2 Replies

  • Kasey_Vincent's avatar
    Kasey_Vincent
    Copper Contributor

    Okay, Never mind. I figured it out. It turns out it was a really simple formula after adding that macro for the PrevSheet object. All I had to do was add +7 to the end of it. I was way overthinking it. Maybe by leaving this here it will help someone else. My formula in the K1 field looks like this now:

     

    =PrevSheet(K1)+7

     

    And the macro as I said earlier was:

    Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
    PrevSheet = Worksheets(xIndex – 1).Range(RCell.Address)
    End Function



    • JoshDavies820's avatar
      JoshDavies820
      Copper Contributor
      Even later to the party... I'm trying to do the same thing but my cell being B2. It seems to run into an error so wondering if you could help?

Resources