Move an entire row to a new sheet based on the value of a cell.

[jgroathouse]

So I know little to nothing about how to work excel so I am really struggling with the last part of my workbook. I want to have it so when a cell in column J of each sheet =OPEN it automatically copies that entire row to the very first sheet "OPEN POSITIONS" that way I am able to see all the orders that are open at each location as well as being able to go to each sheet and see what positions the individual location has open. I've been told I did things backwards and should have kept all the positions together in a table on the first page and then sort them out of the table based on Location but I would rather have it this way if anyone can help me Job Requisition Workbook.xlsx

[walruseggman]

Please Login or Register  to view this content.

[jgroathouse]

I know you're going to think I'm a complete idiot (I guess I am when it comes to excel) but I have no idea where to enter the information you created for me. Thank you so much for your help!

[walruseggman]

• Press Alt+F11. You should see the VBA Editor window pop up.
• On the left side, you should see a Project panel, with your Workbook listed (e.g. VBAProject (Workbook Name))
• In that panel, double click the "ThisWorkbook" Object in your workbook
• Paste the code into the blank white area to the right of the panel.
• Place your cursor somewhere inside the code
• To run the code, press the play button on the tool bar or press F5. The code will run
• Return to your workbook to see the results of running the code.

[jgroathouse]

When I tried that it said "Compile error: Invalid outside procedure"
I'm probably doing something wrong...

[walruseggman]

Post a screenshot that includes the VBA editor window and the error message?

[jgroathouse]

I guess I was doing something wrong, I got this box this time when i tried to run the code what do I do from here?
Capture.jpg

[jgroathouse]

I Doesn't look like the picture attached, I'll try again...
Capture.jpg

[walruseggman]

Wow, i guess i forgot to copy the first line. Put this as your first line, above Dim ws As Worksheet:

Please Login or Register  to view this content.

[jgroathouse]

Yes! That works perfectly.

The only question I have is will I need to re-run the code every time i want that first sheet updated?
-Or is there a way to have it instantly update when the status of a job is changed on one of the location sheets?

[cvishu]

Hi walruseggman,

Thanks for the Nice code you have created, I hope you will suggest how to autoupdate the job status as required by jgroathouse and looking forward to it, also wish if you can modify the code such that OPEN POSITION Sheet can have a permanent Header with Auto Filtering (tried a lot can figure it out).

Hi Jgroathouse,

Till walruseggman give us the solution if you wish you may use the below code and each time you update a value in the sheet job status will be updated:

to be pasted in Thisworkbook

Please Login or Register  to view this content.

The Code is same as given by walruseggman however works every time the sheet is saved.

Further below is the code which will update values asap cell details are changed however it will further make your sheet protected "ie all non blank column of your sheet will become protected and no details can be changed in it, you can still enter value in blank cell but it cant be modified once value is entered, please note even if you just press backspace and then enter you will not be able to enter value in that cell again unless you unprotect the sheet.

You need to paste this code in each sheet separately and have to change the sheet name in formula, you may give different password for different sheet if you wish:

Please Login or Register  to view this content.

Hope i been of help.

Looking forward to walruseggman reply.

Reg,
CVishu

[jgroathouse]

I would be interested in getting a header on that first page too, CVishu.

Let me know if you figured something out!