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

[Kasey_Vincent]

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 · Accepted 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

[#3]

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?