SOLVED

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

Copper Contributor

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?

2 Replies
best response confirmed by Kasey_Vincent (Copper Contributor)
Solution

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



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?
1 best response

Accepted Solutions
best response confirmed by Kasey_Vincent (Copper Contributor)
Solution

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



View solution in original post