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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies