Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
Hi,
I'm getting "variable not defined" on the first line. Help? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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" wrote in message ... Hi, I'm getting "variable not defined" on the first line. Help? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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)" wrote: 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" wrote in message ... Hi, I'm getting "variable not defined" on the first line. Help? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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" wrote in message ... 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)" wrote: 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" wrote in message ... Hi, I'm getting "variable not defined" on the first line. Help? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get date from prior sheet and add 7 days
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! "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... Hi, I'm getting "variable not defined" on the first line. Help? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200783 "cottage6" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i insert todays date in a spread sheet just prior to print | Excel Worksheet Functions | |||
auto highlighting x#of days prior to & after any given date | Excel Worksheet Functions | |||
Weekending Date w/Less Than 3 days Assoc. with Prior Month | Excel Worksheet Functions | |||
Count Days more than 180 days prior to today | Excel Worksheet Functions | |||
Subtracting a time from a day and 2 days prior | Excel Discussion (Misc queries) |