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

Hi Garry

OK thanks for that. I got it working here now , I'm pleased to say. I
think the sheet protection was tripping it up before.

As you asked , I'll try to expand on the issue. As I change the content
of A1 on sheet 1 , so the content of A1 on the other 11 sheets updates
to match it. I was trying to get each the tab names to update
automatically to match the changes made , each tab changing to match the
content of A1.

I had a worksheet in the past which did this. It had coding inserted
under the tabs , which reacted to changes in A1 to rename each tab
accordingly and immediately.

The content of A1 on sheet 1 is critical to the functioning of the whole
project , and changing the content defines the whole point of the
workbook.

You can get it at the link below so you can better see what I'm
describing. It's probably easier that way.

http://www.chayes.demon.co.uk/Perpet...endar_Prac.zip

Enter a month and date into A1 on sheet 1 to see how it works.

The project is pretty much finished apart from the auto-tab-naming idea
, so I'm grateful for your time and expertise.


Bets wishes.

Colin Hayes used his keyboard to write :
Hi Garry

OK thanks for that. I got it working here now , I'm pleased to say. I think
the sheet protection was tripping it up before.

As you asked , I'll try to expand on the issue. As I change the content of A1
on sheet 1 , so the content of A1 on the other 11 sheets updates to match it.
I was trying to get each the tab names to update automatically to match the
changes made , each tab changing to match the content of A1.

I had a worksheet in the past which did this. It had coding inserted under
the tabs , which reacted to changes in A1 to rename each tab accordingly and
immediately.

The content of A1 on sheet 1 is critical to the functioning of the whole
project , and changing the content defines the whole point of the workbook.

You can get it at the link below so you can better see what I'm describing.
It's probably easier that way.

http://www.chayes.demon.co.uk/Perpet...endar_Prac.zip

Enter a month and date into A1 on sheet 1 to see how it works.

The project is pretty much finished apart from the auto-tab-naming idea , so
I'm grateful for your time and expertise.


Bets wishes.

Hi Colin,
You can put code in the Worksheet_Change event behind Sheet1. This,
then, means if you change the date in Sheet1.Range("$A$1") the code
runs to insert the formulas and update the sheet tabs automatically. So
try these changes to your project:

Sub IncrementMonths()
Dim i As Integer
For i = 2 To 3 '12
With Sheets(i)
.Range("$A$1").Formula = _
"=DATE(YEAR('" & Sheets(1).Name _
& "'!$A$1),MONTH('" & Sheets(1).Name & "'!$A$1)+" _
& i - 1 & ",DAY('" & Sheets(1).Name & "'!$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


Behind Sheets(1), paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then IncrementMonths
End Sub

I took a look at your project file. Looks awesome! When I get a chance,
I'll post back any further comments.

--
Garry

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

I've finished reworking your project. would you like me to attach it to
a post OR email to you? If email is prefered post your info something
like...

mymailATsomewhereDOTcom

--
Garry

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

In article , GS writes
I've finished reworking your project. would you like me to attach it to
a post OR email to you? If email is prefered post your info something
like...

mymailATsomewhereDOTcom


Hi Garry

Yes , if you can email it that would be great.

colinATcdandvinylDOTcoDOTuk


Thanks again.



Best Wishes