Hi

I have a small issue that I'm hoping someone can help with.

I have a calendar workbook with 12 sheets , one for each month of the
year.

In B2 on the first sheet the date is entered , for example , as
01/04/2011. This sets the month and year for the first sheet.

My problem is that I'd like the other 11 sheets to update B2 on each
sheet accordingly.

So for example , by entering 01/04/2011 in B2 on sheet 1 :

B2 on sheet 2 would become 01/05/2011
B2 on sheet 3 would become 01/06/2011
B2 on sheet 4 would become 01/07/2011
B2 on sheet 5 would become 01/08/2011
B2 on sheet 6 would become 01/09/2011
B2 on sheet 7 would become 01/10/2011
B2 on sheet 8 would become 01/11/2011
B2 on sheet 9 would become 01/12/2011
B2 on sheet 10 would become 01/01/2012
B2 on sheet 11 would become 01/02/2012
B2 on sheet 12 would become 01/03/2012

An added complication of course is when the year needs to increment too.

Can someone help with some code to insert in cell B2 for my sheets 2 -
12?

Grateful for any help.



Best Wishes

You could insert a formula into B2 on each sheet so it increments the
date entered in Sheet1 respectively. The formula for Sheet2!$B$2 is:

=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+1,DAY(She et1!$B2))
To insert this on the other 10 sheets you must increment the counter in
the 'Month' part of the formula only. (The year will increment
automatically)

----------------------------------------------------------------
To insert this formula appropriately for all 11 sheets via code:
----------------------------------------------------------------

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 12
Sheets(i).Range("$B$2").Formula = _
"=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+" _
& i - 1 & ",DAY(Sheet1!$B2))"
Next
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

In article , GS writes
You could insert a formula into B2 on each sheet so it increments the
date entered in Sheet1 respectively. The formula for Sheet2!$B$2 is:

=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+1,DAY(She et1!$B2))
To insert this on the other 10 sheets you must increment the counter in
the 'Month' part of the formula only. (The year will increment
automatically)

Hi

OK That's perfect - thanks.

BTW - Would it be an easy thing to have the contents of B2 on each sheet
be shown in the tab for the sheet?

So that the Month and Year of the sheet be transferred to become the tab
name?

Thanks again.




----------------------------------------------------------------
To insert this formula appropriately for all 11 sheets via code:
----------------------------------------------------------------

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 12
Sheets(i).Range("$B$2").Formula = _
"=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+" _
& i - 1 & ",DAY(Sheet1!$B2))"
Next
End Sub

You're welcome!

The following revised code will rename each tab to: "mmm_yyyy").

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 12
With Sheets(i)
.Range("$B$2").Formula =
"=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+" _
& i - 1 & ",DAY(Sheet1!$B2))"
.Name = Format(.Range("$B$2").Value, "mmm_yyyy")
End With
Next
Sheets(1).Name = Format(Sheets(1).Range("$B$2").Value, "mmm_yyyy")
End Sub

If you want a different format than just edit that to suit. (I prefer
to NOT use spaces or hyphens in tab names, but that's just my personal
preference)

Doing Sheet1 last will cause Excel to update the formulas to ref the
new sheetname.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry

OK thanks again.

I'm sorry - I couldn't get this working. It gave errors I'm afraid.
Mostly protection errors , even though the sheet and the book are
unprotected. (!)

I ran it as a macro. I hope that's the correct thing to do.

I was hoping to be able to put some code under the tab to have it read
the content of B2. It does need to be volatile so it can change as the
content of B2 on each sheet changes. I think a static macro might not
achieve this so readily , but I'm not all expert.

To confuse matters , I've moved my key date from B2 to A1 , with the
first sheet just called 1. I did modify the code you sent to reflect
this.


Grateful for you help.



In article , GS writes
You're welcome!

The following revised code will rename each tab to: "mmm_yyyy").

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 12
With Sheets(i)
.Range("$B$2").Formula =
"=DATE(YEAR(Sheet1!$B2),MONTH(Sheet1!$B2)+" _
& i - 1 & ",DAY(Sheet1!$B2))"
.Name = Format(.Range("$B$2").Value, "mmm_yyyy")
End With
Next
Sheets(1).Name = Format(Sheets(1).Range("$B$2").Value, "mmm_yyyy")
End Sub

If you want a different format than just edit that to suit. (I prefer
to NOT use spaces or hyphens in tab names, but that's just my personal
preference)

Doing Sheet1 last will cause Excel to update the formulas to ref the
new sheetname.

Colin Hayes brought next idea :
Hi Garry

OK thanks again.

I'm sorry - I couldn't get this working. It gave errors I'm afraid. Mostly
protection errors , even though the sheet and the book are unprotected. (!)

I ran it as a macro. I hope that's the correct thing to do.

I was hoping to be able to put some code under the tab to have it read the
content of B2. It does need to be volatile so it can change as the content of
B2 on each sheet changes. I think a static macro might not achieve this so
readily , but I'm not all expert.

What is it that you're trying to do? I assume it has nothing to do with
the current issue and so more details would be helpful!


To confuse matters , I've moved my key date from B2 to A1 , with the first
sheet just called 1. I did modify the code you sent to reflect this.

Moving the date to $A$1 AFTER the formulas are in place will cause the
formulas to update to reflect this.

Renaming "Sheet1" to "1" AFTER the formulas are in place will cause the
formulas to update to reflect this.

If you made these changes BEFORE running the code then the code should
be modified as follows:

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 12
With Sheets(i)
.Range("$A$1").Formula = "=DATE(YEAR(1!$A$1),MONTH(1!$A$1)+" _
& i - 1 & ",DAY(1!$A$1))"
.Name = Format(.Range("$A$1").Value, "mmm_yyyy")
End With
Next
Sheets(1).Name = Format(Sheets(1).Range("$A$1").Value, "mmm_yyyy")
End Sub

I tested this code before each posting. I don't get the error you
report here as this code works as expected.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc