Macro for Issuing a "Work Order"

[umrebelfan69]

I first off will apologize for the vagueness of the subject of this thread. Im not sure this is where i should post this question.

What I have is a spreadsheet for maintenance on various equipment around my work site. I have the cells that contain the dates of the last maintenance performed conditionally formatted that when it reaches 2 months time after that work, they will turn yellow and alert me that it is time to perform that maintenance again. What i would like to have is a seperate sheet in the workbook that is nothing but "Work Orders", in other words once the cell turns yellow, it will also appear on the seperate sheet. Then I could print that "Work Order" sheet that would only have the certain piece of equipment that needs to be maintained on it, where as the main sheet will have all my equipment. I dont know if i need a formula for this, if so then I have posted the question on the wrong board, or a macro.

If anyone can offer suggestions I would appreciate it. If i need to offer more details let me know.

Thanks
Darryle

[Richard Buttrey]

I first off will apologize for the vagueness of the subject of this thread. Im not sure this is where i should post this question.

What I have is a spreadsheet for maintenance on various equipment around my work site. I have the cells that contain the dates of the last maintenance performed conditionally formatted that when it reaches 2 months time after that work, they will turn yellow and alert me that it is time to perform that maintenance again. What i would like to have is a seperate sheet in the workbook that is nothing but "Work Orders", in other words once the cell turns yellow, it will also appear on the seperate sheet. Then I could print that "Work Order" sheet that would only have the certain piece of equipment that needs to be maintained on it, where as the main sheet will have all my equipment. I dont know if i need a formula for this, if so then I have posted the question on the wrong board, or a macro.

If anyone can offer suggestions I would appreciate it. If i need to offer more details let me know.

Thanks
Darryle

I think I'd be inclined to use a Data-->Filter--> Advanced Filter to extract the 2 month old maintenance items from the main sheet to your works orders sheet. For the criteria cell use the same logic as you've presumably got for your conditional formatting, which checks the data date against the current date. I'd then create a small single line macro to do this, and save the hassle of manually selecting and filtering.

Rgds

[umrebelfan69]

Richard, i appreciate your help. I must tell you though that I gained all my knowledge of Excel from the school of use, trial and error, and reading the posts on this webpage. The stuff you outlined was in terminology i dont understand.

the criteria i used for the conditional formatting is as follows

Condition 1
cell value is...Less than or equal to....=Today()
The formatting is that the cell fills yellow and the font turns bold and red

Condition 2
cell value is...less than or equal to....today()-305
The formatting is that the font turns red and bold
this condition is to alert me that the maintenance is in 2 months time and for me to make sure i have the parts on hand.

condition 1 is the one i need the macro for. once the cell turns yellow, id like it to also appear on a seperate sheet that i can print out and give to my employees to perform the work order.

I am sorry that I dont completely understand all the Excel terminology. I can do alot with Excel but its only from trial and error did i learn how.

once again. thanx for your help

Darryle

[Richard Buttrey]

Richard, i appreciate your help. I must tell you though that I gained all my knowledge of Excel from the school of use, trial and error, and reading the posts on this webpage. The stuff you outlined was in terminology i dont understand.

the criteria i used for the conditional formatting is as follows

Condition 1
cell value is...Less than or equal to....=Today()
The formatting is that the cell fills yellow and the font turns bold and red

Condition 2
cell value is...less than or equal to....today()-305
The formatting is that the font turns red and bold
this condition is to alert me that the maintenance is in 2 months time and for me to make sure i have the parts on hand.

condition 1 is the one i need the macro for. once the cell turns yellow, id like it to also appear on a seperate sheet that i can print out and give to my employees to perform the work order.

I am sorry that I dont completely understand all the Excel terminology. I can do alot with Excel but its only from trial and error did i learn how.

once again. thanx for your help

Darryle

Hi,

I thought the only 'terminology' I'd used was Data-->Filter-->Advanced, which is a fairly common process available from the Excel Menu for doing exactly what you want.

It would help if you could attach your workbook here, and put a few notes of explanation in it indicating the data you're trying to extract. I'll then take a look.

Rgds

[umrebelfan69]

Ok here it goes
That is a picture of the file, the file itself is 3mb. When looking at the picture you will see that on the far right hand side is a column marked "Scheduled Change Out". When it turns yellow it is time for the change out. Basically what i would like to do is create another sheet (Work Order Sheet) in this workbook that either searches automatically or by user commanded macro the entire workbook for yellow (Warning) cells. If it finds them it puts the entire ROW onto the "work order" sheet.

What threw me off with the "Terminology" is that you told me to "For the criteria cell use the same logic as you've presumably got for your conditional formatting". The type of info i used in the conditionally formatting would not work in the "Criteria cell" so i thought i might have mis understood your meaning.

Thanks
Darryle

[Richard Buttrey]

Ok here it goes
That is a picture of the file, the file itself is 3mb. When looking at the picture you will see that on the far right hand side is a column marked "Scheduled Change Out". When it turns yellow it is time for the change out. Basically what i would like to do is create another sheet (Work Order Sheet) in this workbook that either searches automatically or by user commanded macro the entire workbook for yellow (Warning) cells. If it finds them it puts the entire ROW onto the "work order" sheet.

What threw me off with the "Terminology" is that you told me to "For the criteria cell use the same logic as you've presumably got for your conditional formatting". The type of info i used in the conditionally formatting would not work in the "Criteria cell" so i thought i might have mis understood your meaning.

Thanks
Darryle

Hi,

It's a little difficult to read that picture of the file. Is it possible that you could zip up the workbook and attach it? Cut out any irrelevant sheets or data rows if that helps, but it's always nice to see things at first hand.

Rgds

[umrebelfan69]

or any other compressing agent. i got the actual file down to a 25k file but cant attach a xls

[Richard Buttrey]

or any other compressing agent. i got the actual file down to a 25k file but cant attach a xls

You should find that if you right click on the name of the file in Windows Explorer, you get an option to zip the file. You can then attach the zipped file.

Rgds

[umrebelfan69]

Thanks Richard.

Here is the file, as you can see hoist 1 is all fine. hoist 2 is scheduled for change out. i would like the second sheet (Work Orders) to search the first sheet (Hoisting) for the yellow warning cells. of course it doesnt have to search for yellow cells but can search for the same criteria that makes those cells turn yellow. once it finds one that meets the criteria i would like that piece of equipment to show up on the work order sheet. i could then once a week print the work order sheet which would only display the equipment needing maintenance, otherwise i have to print the entire (hoisting page).

Thanks for your patience

Darryle

[umrebelfan69]

Richard,
Here is a little bit better look at what i want it to do. as you can see on the file, on the rig floor sheet, the stabbing board hoist line is due for change out, all other gear on the rig floor sheet is in good shape. on the crane single sling sheet, only the personnel basket is due for change out. lookign on the work order sheet, you will see only the two items needing changed out. this is what i would liek to automatically occur when the criteria that triggers the conditional format is met. i hope this helps more. it is killing me to not know how to make this work

thanks
Darryle

[umrebelfan69]

I dont know what happened. It showed that it was attached then when i posted it, it didnt show up. ill try again

[jmicdk]

Would make it a lot easier to do if you didn't merge cells and columns like you did.

I don't know how to declare all those merged cells as range.

If those rows weren't merged like it is then something like this may work.

Paste this in a module and run.

Maybe if you unmerge it and change the routine slightly it can have the desired effect.

Please Login or Register  to view this content.

[Richard Buttrey]

Would make it a lot easier to do if you didn't merge cells and columns like you did.

I don't know how to declare all those merged cells as range.

If those rows weren't merged like it is then something like this may work.

Paste this in a module and run.

Maybe if you unmerge it and change the routine slightly it can have the desired effect.

Hi umrebelfan69,

Let us know if jmicdk's solution works for you.

I concur with his comment that all those merged cells are just a nuisance factor. I had started on a solution which adopted the more traditional approach of keeping one piece of data in one cell and using Data Filter, but if this works all well and good.

Just as a matter of interest, was there a reason for merging all those cells?

Rgds

[umrebelfan69]

This sheet is for designing purposes. i have found that sometimes i actually need cells inside of cells, for like header cells. when i am designing spreadsheets i use multiple small cells so i can design them the way and the size i need. sometimes i need one cell to be one size but the one beside it i need it another size. in other words if i enlarge the row height, then the entire row is resized. if i enlarge the column the same happens with columns. but now that i have the page designed the way i wish for it to look, i can go back and rebuild using single cells. I hope to get a little time to work on this project in the next couple of days, i will then try the code out. i appreciate all of you guys help on this subject. my job has me snowed under for the next couple of days or i would be trying it right now. i am anxious to see how it works. thanks again guys