# Help with changing the year

#### locoff

##### New Member
Hi,

I'm looking for help with a code problem. In my sheet I have a cell that has the year in 4 digits plus 5 other digits for incidents in our fire dept. (ie 2008#####) what I want is to have the year automatically change to 2009 on the first day of the new year. Is this possible? If so how?

As always any help would be greatly appreciated!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### njimack

##### Well-known Member
This assumes the cell to be changed is in Sheet1 A1. This code will run when you open the file. You need to paste it into the ThisWorkbook module

Code:
``````Private Sub Workbook_Open()

If Day(Date) = 1 And Month(Date) = 1 Then
With Sheets("Sheet1").Range("A1")
.Value = Year(Date) & Right(.Value, 5)
End With
End If

End Sub``````

#### locoff

##### New Member
Neil,

Thanks for the code. Just 1 more question, do I use a range if it's used with multiple sheets ie... With Sheets("Sheet1:Sheet31").Range("A1")

is this correct?

Donnie

#### njimack

##### Well-known Member
Wouldn't it be much simpler to have Sheets 2 to 30 linked to Sheet 1?

Neil,

Thanks for the code. Just 1 more question, do I use a range if it's used with multiple sheets ie... With Sheets("Sheet1:Sheet31").Range("A1")

is this correct?

Donnie

#### locoff

##### New Member
neil,

Sorry I had to step away for awhile. You are right and I will link the other sheets to sheet 1.

I am having a problem with the code. When I run the prog. I get a runtime error '9' subscript out of range which puts me in the "with" line of the code yellow hi-lited.
btw I have set the computer date to 1/1/09.

Donnie

#### njimack

##### Well-known Member
Is there actually a sheet called Sheet1?

I am having a problem with the code. When I run the prog. I get a runtime error '9' subscript out of range which puts me in the "with" line of the code yellow hi-lited.
Donnie

#### locoff

##### New Member
Yes, they are numbered "Sheet1" up to "Sheet31" for days in a month.

#### njimack

##### Well-known Member
that makes no sense - I've just tested on a new workbook and it works perfectly.

The only way I can replicate that error is by renaming sheet1. Are you sure there are no leading or trailing spaces in the sheet name?

#### locoff

##### New Member
Just to be clear, here is what I have:

Private Sub Workbook_Open()
If Day(Date) = 1 And Month(Date) = 1 Then
With Sheets("Sheet1").Range("D21")
.Value = Year(Date) & Right(.Value, 5)
End With
End If

End Sub

#### locoff

##### New Member
Thanks to all for your help! Problem solved!

Replies
0
Views
329
Replies
2
Views
249
Replies
6
Views
193
Replies
3
Views
316
Replies
1
Views
253

1,191,093
Messages
5,984,635
Members
439,897
Latest member
osman53

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back