Unfortunatly, I am also not experienced enough to figure out what I have
done wrong. Please take a look at this horrible code and tell me what I have
done wrong.

The point of this is to automatically update information when the workbook
is opened. This is a time off tracking sheet. there is a master blank copy
used for creating new sheets. A census form that is a repository for other
general informatoin for all employees. Vac&Sick sheet is an index similiar
to the census form. Each remaining sheet is the individuals record fo time
taken off.

Thanks for helping and if you have any questions let me know. By the way,
this started with another thread but I have lost it somehow. For whatever
reason I am unable to access my old thread. Sorry for any confusion or
reposting that has occured.
LWhite

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.wks
' pick an employee record to evaluate rather than the master and system
sheets
'
If LCase(wks.Name) = LCase("master") Then
' do nothing
Else
If LCase(wks.Name) = LCase("employee census") Then
' do nothing
Else
If LCase(wks.Name) = LCase("vac&sick") Then
' do nothing
Else

' Determine if the employee uses the first of the year or not

If ActiveSheet.Range("C5").Value DateSerial(2005, 1, 1) Then
' if they do then do the copy/move
If Range("A99").Value 0 Then
Range("A7:F34").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=66
Range("A100").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-75
Range("A8:F34").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A99").Value = 1
End If
Else
' if not then determine if the copy should be done or not
If ActiveSheet.Range("C5").Value DateSerial(Now - 1, Range("C5"),
Range("C5")) Then
If Range("A99").Value 0 Then
Range("A7:F34").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=66
Range("A100").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-75
Range("A8:F34").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A99").Value = 1
End If
End If
End If
End If
End If
End If

Next wks
End Sub

What exactly is the problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"L.White" wrote in message
...
Unfortunatly, I am also not experienced enough to figure out what I have
done wrong. Please take a look at this horrible code and tell me what I
have
done wrong.

The point of this is to automatically update information when the workbook
is opened. This is a time off tracking sheet. there is a master blank copy
used for creating new sheets. A census form that is a repository for other
general informatoin for all employees. Vac&Sick sheet is an index similiar
to the census form. Each remaining sheet is the individuals record fo time
taken off.

Thanks for helping and if you have any questions let me know. By the way,
this started with another thread but I have lost it somehow. For whatever
reason I am unable to access my old thread. Sorry for any confusion or
reposting that has occured.
LWhite

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.wks
' pick an employee record to evaluate rather than the master and system
sheets
'
If LCase(wks.Name) = LCase("master") Then
' do nothing
Else
If LCase(wks.Name) = LCase("employee census") Then
' do nothing
Else
If LCase(wks.Name) = LCase("vac&sick") Then
' do nothing
Else

' Determine if the employee uses the first of the year or not

If ActiveSheet.Range("C5").Value DateSerial(2005, 1, 1) Then
' if they do then do the copy/move
If Range("A99").Value 0 Then
Range("A7:F34").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=66
Range("A100").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-75
Range("A8:F34").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A99").Value = 1
End If
Else
' if not then determine if the copy should be done or not
If ActiveSheet.Range("C5").Value DateSerial(Now - 1, Range("C5"),
Range("C5")) Then
If Range("A99").Value 0 Then
Range("A7:F34").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=66
Range("A100").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-75
Range("A8:F34").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A99").Value = 1
End If
End If
End If
End If
End If
End If

Next wks
End Sub

Sorry about that.

To test this I changed the date in my machine to be past the first of next
year. If this were written correctly all people hired before 2005 would have
had the days off moved to a lower position on their sheet and the main usage
area blanked. Nothing happened at all.

LWhite

"Bob Phillips" wrote in message
...
What exactly is the problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)

Maybe it is because you have fully qualified all objects. Try this

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.wks
' pick an employee record to evaluate rather than the master and system
Sheets
With wks
If LCase(.Name) = "master" Or _
LCase(.Name) = "employee census" Or _
LCase(.Name) = "vac&sick" Then
' do nothing
ElseIf .Range("C5").Value DateSerial(2005, 1, 1) Or _
.Range("C5").Value DateSerial(Now - 1, .Range("C5"),
..Range("C5")) Then
' Determine if the employee uses the first of the year or
' if not then determine if the copy should be done or not
If .Range("A99").Value 0 Then
.Range("A7:F34").Copy .Range("A100")
.Range("A8:F34").ClearContents
.Range("A99").Value = 1
End If
End If
End With
Next wks
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"L.White" wrote in message
...
Sorry about that.

To test this I changed the date in my machine to be past the first of next
year. If this were written correctly all people hired before 2005 would
have
had the days off moved to a lower position on their sheet and the main
usage
area blanked. Nothing happened at all.

LWhite

"Bob Phillips" wrote in message
...
What exactly is the problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)

That should be NOT fully qualified.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Maybe it is because you have fully qualified all objects. Try this

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.wks
' pick an employee record to evaluate rather than the master and system
Sheets
With wks
If LCase(.Name) = "master" Or _
LCase(.Name) = "employee census" Or _
LCase(.Name) = "vac&sick" Then
' do nothing
ElseIf .Range("C5").Value DateSerial(2005, 1, 1) Or _
.Range("C5").Value DateSerial(Now - 1, .Range("C5"),
.Range("C5")) Then
' Determine if the employee uses the first of the year or
' if not then determine if the copy should be done or not
If .Range("A99").Value 0 Then
.Range("A7:F34").Copy .Range("A100")
.Range("A8:F34").ClearContents
.Range("A99").Value = 1
End If
End If
End With
Next wks
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"L.White" wrote in message
...
Sorry about that.

To test this I changed the date in my machine to be past the first of
next
year. If this were written correctly all people hired before 2005 would
have
had the days off moved to a lower position on their sheet and the main
usage
area blanked. Nothing happened at all.

LWhite

"Bob Phillips" wrote in message
...
What exactly is the problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)

I am receiving a compiler error of End With without With. If I comment out
the End With I receive the same error saying Next without For Each. I don't
get that since both statements are clearly there. Here is the code I have in
place now.


Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.wks
' pick an employee record to evaluate rather than the master and system
Sheets
With wks
If LCase(.Name) = "master" Or _
LCase(.Name) = "employee census" Or _
LCase(.Name) = "vac&sick" Then
' do nothing
Else
If .Range("C5").Value DateSerial(2005, 1, 1) Or _
.Range("C5").Value DateSerial(Now - 1, .Range("C5"),
..Range("C5")) Then
' Determine if the employee uses the first of the year or
' not then determine if the copy should be done or not
If .Range("A99").Value = 0 Then
.Range("A7:F34").Copy .Range("A100")
.Range("A7:F34").ClearContents
.Range("A99").Value = 1
End If
End If
End With
Next wks
End Sub

By the way Bob, I like the way that this code looks a lot better than what I
had before. This is much cleaner and easier to read. Thanks for helping.
Leonard

Oh yeah, the code I posted a moment ago is in the ThisWorkbook section of
the spreadsheet. Is that the correct location?

Leonard

I tried responding to this thread earlier but nobody answered. I am trying
again before restarting the thread.

I want the following code to automatically execute when file opens. Right
now the code is located in the ThisWorkbook location. Why isn't it running
on open?

LWhite

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
'pick employee record to evaluate rather than master and system Sheets
With wks
If LCase(.Name) = "master" Or _
LCase(.Name) = "employee census" Or _
LCase(.Name) = "vac&sick" Then
' do nothing
ElseIf .Range("C5").Value < DateSerial(2005, 1, 1) Or _
.Range("C5").Value < Now - 365 Then
' Determine if the employee uses the first of the year or
' if not then determine if the copy should be done or not
If .Range("A99").Value = 0 Then
.Range("A7:F34").Copy .Range("A100")
.Range("A8:F34").ClearContents
.Range("A99").Value = 1
End If
End If
End With
Next wks
End Sub

I responded suggesting you mail it to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"L.White" wrote in message
...
I tried responding to this thread earlier but nobody answered. I am trying
again before restarting the thread.

I want the following code to automatically execute when file opens. Right
now the code is located in the ThisWorkbook location. Why isn't it running
on open?

LWhite

Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
'pick employee record to evaluate rather than master and system Sheets
With wks
If LCase(.Name) = "master" Or _
LCase(.Name) = "employee census" Or _
LCase(.Name) = "vac&sick" Then
' do nothing
ElseIf .Range("C5").Value < DateSerial(2005, 1, 1) Or _
.Range("C5").Value < Now - 365 Then
' Determine if the employee uses the first of the year or
' if not then determine if the copy should be done or not
If .Range("A99").Value = 0 Then
.Range("A7:F34").Copy .Range("A100")
.Range("A8:F34").ClearContents
.Range("A99").Value = 1
End If
End If
End With
Next wks
End Sub