Forum Discussion
(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 SubTried 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
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?
