Solved: An End of Year function

[Aussiebear]

Is it easier ( better programaticily) to build an "End of Year" function to copy and paste one sheet to another, using the date as the trigger, or simply to rename the sheet and have the second sheet assume the role that the first one was undertakiing?

At present, tasks being completed are C & P to Sheet "Completed Work", but as at the 1/1/07, all data on this sheet will need to be stored on Sheet "2006". Data on sheet "Completed Work" is then cleared. Subsequently once 1/1/08 occurs all data on "Completed Work" sheet will then be copied to sheet "2007" and so on.

The reasoning here is that the person responcible for the main operations of the workbook will not be the "Administrator" of the workbook, and the more that could be automated the better.

Ted

[mdmackillop]

I would keep a blank "Template" sheet as a hidden sheet. This sheet would be copied and renamed to suit. Completed Work would simply be renamed. This could all be accomplished within as fairly simple macro.
You might also wish to incorporate Protection, to lock historical Year sheets against accidental changes/deletion.

[Aussiebear]

Well now, fancy you knowing what it is that I'm chasing.... LOL.

[mdmackillop]

I'm running a similar routine for timesheets, except that I move past timesheets into an Archive file, stripping out all code and formulae, where they can be analysed. It reduces the "Current" workbook size and keeps the data safer.

[Bob Phillips]

I'm running a similar routine for timesheets, except that I move past timesheets into an Archive file, stripping out all code and formulae, where they can be analysed. It reduces the "Current" workbook size and keeps the data safer.
Why not start with separate data and code files, saves all that stripping out which is messy. Abstraction is the key!

[mdmackillop]

Most of my code is in a shared xla file already, there are some worksheet events and worksheet formulae which seemed simpler to manage in each person's workbook, but I'll bear your suggestion in mind when I next revisit it.

[Aussiebear]

Do I have to unhide the sheet to copy it?

[cjyogz]

No, you don't have to unhide a sheet to copy it, all you have to do is refer to the sheet by its name and it will copy the aquired data

[mdmackillop]

Do I have to unhide the sheet to copy it?
Don't know! Give it a try.

[Aussiebear]

I have had an attempt to code this, so all who need a laugh please read on.


Private Sub EndofYear()
Application.ScreenUpdating = False ' I read somewhere that all macro's need this line up front
Sheets.Add
' Move new sheet to last position in Workbook and rename "2006"
ActiveSheet.Move(After:=Sheets(Sheet.Count)) .Name "2006"
Sheets("Completed Work").Select
Selection.Copy
Sheets("2006").Select
Cells.Select
Selection.PasteSpecial Paste:= xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Protect Password:="Password", DrawingObjects:=True,Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
End Sub


Now that you've all stopped rolling about the floor from laughter, there a couple of problems here that I've noticed like... Run Time Error 424 on the ActiveSheet.Move ...line.

And I've not set the trigger for this to occur. I had thought that I could try and set two conditions If Year = 2007 and IF sheet "2006" doesn't exist then run macro otherwise exit sub, but this doesn't help me the next year.

... but then I had a go I guess

[Aussiebear]

Or the other option based on copying a hidden sheet ("Work")


Private Sub EndofYear()
Application.ScreenUpdating = False
' Rename sheet "Completed Work" as "2006"
Sheets("Completed Work").Name = "2006"
Sheets("Work").Copy, .Name "Completed Work"

Application.CutCopyMode = False
ActiveSheet.Protect Password:= "Password", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
End Sub


Still no trigger, but it will be hopefully based on the criteria as posted in the one above.

(Retreating back to my cave now...)

[mdmackillop]

A good attempt.
Screen updating is not essential. What is does is to prevent flickering as the code rapidly switches pages and writes data. This speed up code execution. For a small, once a year item like this, its good to have, but not absolutely necessary.
There is an error on this line
ActiveSheet.Move(After:=Sheets(Sheet.Count)) .Name "2006"

.Name has to be contained within a With statement (see below) and you're missing an "="
Sheets("Completed Work").Select
Selection.Copy

This will not copy the contents of the sheet. You need to add in Cells.Copy

Here's a working version


Private Sub EndofYear()
Application.ScreenUpdating = False ' I read somewhere that all macro's need this line up front
Sheets.Add
' Move new sheet to last position in Workbook and rename "2006"
With ActiveSheet
.Move After:=Sheets(Sheets.Count)
.Name = "2006"
End With
Sheets ("Completed Work")
Cells.Select
Selection.Copy
Sheets("2006").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
End Sub

[mdmackillop]

It is rare that you need to Select cells etc. in VBA to run the code. If you can avoid this, it actually simplifies things and speeds up execution. The Copy routine you have can be simplified to two lines

Sheets("Completed Work").Cells.Copy
Sheets("2006").Cells.PasteSpecial Paste:=xlPasteAll

If you create code using the macro recorder (as I do all the time) try to edit it to remove the selection steps. It just takes a little practice.

[mdmackillop]

This is how I would handle the Template version

Sub Work()
Sheets("Completed Work").Name = Year(Now()) - 1
With Sheets("work")
.Visible = True
.Copy Before:=Sheets(1)
ActiveSheet.Name = "Completed Work"
.Visible = False
End With
End Sub

[mdmackillop]

If you really need a trigger try

Private Sub Workbook_Open()
Dim ws As Worksheet, Yr As String
Yr = CStr(Year(Now()) - 1)
On Error Resume Next
Set ws = Sheets(Yr)
If Err <> 0 Then Work
End Sub

[Aussiebear]

If you really need a trigger try

Private Sub Workbook_Open()
Dim ws As Worksheet, Yr As String
Yr = CStr(Year(Now()) - 1)
On Error Resume Next
Set ws = Sheets(Yr)
If Err <> 0 Then Work
End Sub



Hmmm... I'm guessing this tests for a sheet called ( whatever the current year is minus 1) and if it doesn't find it then it opens the sheet called "Work"?

Should the trigger code occupy the lines in front of those in the template version?

[mdmackillop]

The trigger goes into the ThisWorkbook module. You can put the "Work" code in there as well.

[Aussiebear]

Malcolm, can you have another look at the way in which the Expected date is calculated from the Raised by date. Its seems to be going screwy.

Dates are shown as U.S. format and when the corresponding value for each term is added, the dates shown are all over the shop

Ted