This is not working because I didn't write it correctly
Leonard,
It may have been the wrap-around causing it.
I have looked at the code again and there are a couple of other problems,
hopefully all corrected here
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 - 1 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
and yes, it does go in ThisWorkbook.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"L.White" wrote in message
...
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
|