Get date from prior sheet and add 7 days

[cottage6]

Hi Everyone,
I'm rewritting an old .123 file to Excel. There's lots of sheets and all
have a hard-coded date in cell C3. I want to start with the last hard-coded
date (April 26 sheet) and start using a formula to increment that date by 7
days to come up with May 3, May 10 on the next new sheet, and so on.
VB code I've rewritten so far copies a template sheet (no date in the
template in c3) to a new sheet and adds it to the left of the April 26 sheet,
sets C3 in the new sheet equal to some text I'm just goofing around with to
test, and then renames the sheet to the value in C3. I really need the value
in C3 to equal C3 from the prior sheet + 7. I've included the code below.
Thanks!
Sub NewSheet()
Sheets("Blank Template").Select
Sheets("Blank Template").Copy AFTER:=Sheets(2)
Range("C3").Select
ActiveCell.FormulaR1C1 = "go Crunch!"
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.Name = wks.Range("C3").Value
Next wks

 

[Gary''s Student]

Run this macro; it determines the preceding sheet, gets the date from that
preceding sheet, and sets the date cell on the current sheet:

Sub setdate()
n = ActiveSheet.Name
For i = 1 To Sheets.Count
If n = Sheets(i).Name Then Exit For
Next
Sheets(i).Range("C3").Value = Sheets(i - 1).Range("C3") + 7
End Sub

Just don't run it on the first sheet.

 

[cottage6]

Hi,
I'm getting "variable not defined" on the first line. Help?

 

[Rick Rothstein \(MVP - VB\)]

Good, that means you are using Option Explicit. The warning message mean you
are trying to use a variable for which there is no Dim statement. Gary''s
Student omitted them. Add these to the "setdate" code...

Dim n As String
Dim i As Long

and the error messages should go away.

Rick

 

[cottage6]

Hey Rick,
Hello and how are you since the last time you helped me? I should have
known enough to put in those 2 Dim statments; I think I've been looking at
this too long. The code runs fine now, but the date in the new sheet is
1/7/1900. Any ideas?
And Gary, thanks to you also for the code you sent.

 

[Rick Rothstein \(MVP - VB\)]

I've been fine, thank you. It looks like you do not have a date, but rather
a day number within a month (an 8 I am guessing). VB uses a Double to store
its dates where the whole number part is an offset from "date zero"
(December 30, 1899 in the VBA world) and the fractional part is the fraction
of a 24-hour day past midnight. So, if you attempted to coerce an 8 to a
date, you would get 8 days past date zero. You can see this from the
following VBA statement...

MsgBox CDate(8)

I'm not sure exactly what the fix is as I don't know your data, but the
general solution would be to use the DateSerial function to create your date
feeding it the correct year and month numbers along with the day number.

Rick

 

[cottage6]

I think I'm going to cut my losses and move on. I gave them a drop-down list
of dates they can choose from, so that's better than it was. I've spent more
time on this than I needed to but I was really curious. I did pick up some
good code I can use other places I'm sure. Thanks again to both you and Gary
for your help. Have a good day!