Help with changing the year

locoff

New Member
Joined
Jun 30, 2008
Messages
10
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top