SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-772796%22%20slang%3D%22en-US%22%3E(Excel)%20Pulling%20date%20from%20another%20sheet%2C%20adding%20one%20week%20to%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772796%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20I%E2%80%99m%20a%20little%20late%20to%20the%20party%20here%2C%20but%20Im%20trying%20to%20pull%20a%20date%20from%20a%20previous%20sheet%20and%20add%20it%20to%20the%20next%20sheet%2C%20while%20adding%207%20days%20to%20the%20date.%20Is%20there%20a%20macro%20on%20how%20to%20do%20this%3F%20I%E2%80%99ve%20already%20found%20a%20Macro%20to%20pull%20information%20from%20a%20previous%20sheet%2C%20but%20I%E2%80%99m%20trying%20to%20use%20it%20to%20add%20days..%20And%20I%20can%E2%80%99t.%20The%20cell%20I%E2%80%99m%20using%20is%20K1%20and%20I%E2%80%99ve%20tried%20every%20combination%20I%20can%20think%20of%20with%20everything%20that%E2%80%99s%20listed%20here%3A%3C%2FP%3E%3CP%3EAdded%20and%20modified%20this%20Macro%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3ESub%20Add_Day_To_Date()%0A'Adds%201%20to%20the%20active%20cell%0A%0A%26nbsp%3B%26nbsp%3BActiveCell.Value%20%3D%20ActiveCell.Value%20%2B%201%0A%0AEnd%20Sub%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3ETried%20using%2C%20modifying%2C%20and%20using%20variations%20of%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DDATE(YEAR(A1)%2CMONTH(A1)%2CDAY(A1)%2B7)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3EI've%20also%20tried%20the%20Paste%20special%20trick%2C%20but%20it%20doesn't%20seem%20to%20work%20when%20dealing%20with%20another%20sheet..%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20macro%20I%E2%80%99m%20currently%20using%20is%3A%3C%2FP%3E%3CPRE%3EFunction%20PrevSheet(RCell%20As%20Range)%3CBR%20%2F%3EDim%20xIndex%20As%20Long%3CBR%20%2F%3EApplication.Volatile%3CBR%20%2F%3ExIndex%20%3D%20RCell.Worksheet.Index%3CBR%20%2F%3EIf%20xIndex%20%26gt%3B%201%20Then%20_%3CBR%20%2F%3EPrevSheet%20%3D%20Worksheets(xIndex%20%E2%80%93%201).Range(RCell.Address)%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%20What%20would%20the%20formula%20or%20macro%20be%20that%20would%20give%20me%20the%20result%20I%E2%80%99m%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772796%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772804%22%20slang%3D%22en-US%22%3ERe%3A%20(Excel)%20Pulling%20date%20from%20another%20sheet%2C%20adding%20one%20week%20to%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772804%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%2C%20Never%20mind.%20I%20figured%20it%20out.%20It%20turns%20out%20it%20was%20a%20really%20simple%20formula%20after%20adding%20that%20macro%20for%20the%20PrevSheet%20object.%20All%20I%20had%20to%20do%20was%20add%20%2B7%20to%20the%20end%20of%20it.%20I%20was%20way%20overthinking%20it.%20Maybe%20by%20leaving%20this%20here%20it%20will%20help%20someone%20else.%20My%20formula%20in%20the%20K1%20field%20looks%20like%20this%20now%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DPrevSheet(K1)%2B7%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20the%20macro%20as%20I%20said%20earlier%20was%3A%3C%2FP%3E%3CPRE%3EFunction%20PrevSheet(RCell%20As%20Range)%3CBR%20%2F%3EDim%20xIndex%20As%20Long%3CBR%20%2F%3EApplication.Volatile%3CBR%20%2F%3ExIndex%20%3D%20RCell.Worksheet.Index%3CBR%20%2F%3EIf%20xIndex%20%26gt%3B%201%20Then%20_%3CBR%20%2F%3EPrevSheet%20%3D%20Worksheets(xIndex%20%E2%80%93%201).Range(RCell.Address)%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kasey_Vincent
New 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?

1 Reply
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



Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies