Realizing Lewis's idea:
Sheet1 columns:
A B C D
E F BD
Date of
w e e k s
Reg. Fleet No. Last service Interval W01 W02 ... W52
xxx xxx mm/dd/yyyy 4or5or6
Sheet2 columns
A B
First Monday 03/01/2005
The subs:
Sub allrows()
vehnum = Cells.Find("*", Range("A1"), xlValues, xlPart, xlByRows,
xlPrevious, False).Row
For v = 2 To vehnum
Call servmark(v)
Next v
End Sub
Sub singlerow()
v = ActiveCell.Row
Call servmark(v)
End Sub
Sub servmark(vehrow)
Dim vehicle, week As Range
firstmonday = Sheets("Sheet2").Range("B1")
servintval = Cells(vehrow, 4) '4,5,6
Set vehicle = Range(Cells(vehrow, 5), Cells(vehrow, 56)) '"E2:BD2"
vehicle.ClearContents
weeknum = 0
For Each week In vehicle
weeknum = weeknum + 1
lastservice = Cells(vehrow, 3) '"C2"
weekstart = firstmonday + (weeknum - 1) * 7
weekend = firstmonday + weeknum * 7
servivs = 52 \ servintval + IIf(52 Mod servintval 0, 1, 0) '13,
10,4 8,67
For i = 1 To servivs
dueservice = lastservice + i * servintval * 7
If dueservice = weekstart And dueservice < weekend Then
week.Value = "X"
Exit For
Else
week.Value = ""
End If
Next i
Next week
End Sub
Then Autofiltering week columns you will see the vehicles to be serviced in
that week. Don't forget to rerun macros after inserting new vehicle or
changing date of last service!
Regards,
Stefi
€˛Lewis Clark€¯ ezt Ć*rta:
Here is another option to consider:
If I understand correctly, your 52 weekly worksheets simply list the vehicle
name and a serial number. Why not put all that data into a single worksheet
with 54 columns: one column for the name, one for the serial number and one
for each week. Then you could put an "x" into the cells to indicate which
weeks each vehicle needs service. Autofilters would then allow you to look
at the list for any particular week.
"kevhatch" wrote in message
...
All the vehicles we have are set in their respective sevice routines, what
I
would like to be able to do is to open up a particular week to view which
vehicles are coming up for service. Also, as we acquire new vehicles, I
could
enter them into the 4,5, or 6 week(as required by type of vehicle) sheet,
then enter reg/fleet No. into the first service week which would then
automatically follow on.
Hope this makes it a bit clearer.
Thx
Kev
"Stefi" wrote:
Hi kevhatch,
Your request isn't quite clear. Do you want to add those vehicles to
weekly
sheets which are due to be serviced in that particular week? Otherwise
put:
Do you want weekly sheets showing vehicles to be serviced in that week?
If
so, do you have the date of the last service for each vehicle?
It doesn't seem necessary to input reg No.-s, but input week No. and ask
Excel (write a macro) to collect reg No.-s of vehicles to be serviced in
that
week into the corresponding week sheet (that is those with which the
service
interval elapsed since the last service date).
Regards,
Stefi
"kevhatch" ezt Ć*rta:
We have a fleet of vehicles that are serviced at 4week, 5week and 6week
intervals. I would like a workbook with 3 sheets covering the different
service intervals and 52 sheets, week1,2,3....52.
The 4,5 and 6 week service sheets would have the vehicle reg and fleet
No.
What I would like it to be able to do is for me to input a vehicle
reg/fleet
No. into any of the weekly sheets and excell to check if it is 4,5 or 6
week
service and add it to the weekly sheets accordingly. This is way above
my
head :-( any help if it can be done most welcome.
Thx in advance.
Kev
|