This is not working because I didn't write it correctly

[L.White]

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

[Bob Phillips]

What exactly is the problem?

--

HTH

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


"L.White" <lwhite@appliedcomposites.com> wrote in message
news:OLeF0qu6FHA.4076@tk2msftngp13.phx.gbl...
> 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
>
>

[L.White]

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O8wUU2u6FHA.3416@TK2MSFTNGP15.phx.gbl...
> What exactly is the problem?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)

[Bob Phillips]

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" <lwhite@appliedcomposites.com> wrote in message
news:ODATbCv6FHA.2012@TK2MSFTNGP14.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O8wUU2u6FHA.3416@TK2MSFTNGP15.phx.gbl...
> > What exactly is the problem?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
>
>

[Bob Phillips]

That should be NOT fully qualified.

--

HTH

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


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OLitAov6FHA.1188@TK2MSFTNGP12.phx.gbl...
> 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" <lwhite@appliedcomposites.com> wrote in message
> news:ODATbCv6FHA.2012@TK2MSFTNGP14.phx.gbl...
> > 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:O8wUU2u6FHA.3416@TK2MSFTNGP15.phx.gbl...
> > > What exactly is the problem?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> >
> >
>
>

[L.White]

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

[L.White]

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

Leonard

[L.White]

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

[Bob Phillips]

I responded suggesting you mail it to me.

--

HTH

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


"L.White" <lwhite@appliedcomposites.com> wrote in message
news:%23TwaG1D8FHA.3544@TK2MSFTNGP09.phx.gbl...
> 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
>
>