Button to search workbook for A1="x", if true, then copy Row4 to Sheet1

Hi there,
I am new to VBA/macros, but understand that the capabilities of it are near endless.

I am trying to achieve a Production Schedule that I can email to contractors on a weekly basis - without the need to manually (and laboriously) cut&paste key data.

I have scoured the web for any previously designed solutions for excel and haven't been able to find one that suits my application. Hopefully, someone can help me out...

Attached is a sample workbook. Sheet1 shows the desired result of what I wish to achieve. Sheet2 onwards is the data to search.

I require a BUTTON on Sheet1 that I can activate to populate the production schedule. This needs to search the entire workbook for cell A1="In Production" (note: cell A1 is a dropdown); if true, it will then copy Row4 to the next available row+1 on Sheet1. If A1="Complete" no action is taken and the search continues through the workbook. When the search/import is complete, I would like Sheet1 to be saved as a PDF named "Production Schedule_dd-mm-yy" (dd-mm-yy being the current day).

From my research, I found that it would get VERY complex if I wish Sheet1 to auto update when I change any cell A1 value. So, I propose that when producing the weekly schedule: I will update all necessary cell A1s; then delete the previous week's "imported" data from Sheet1 (eg Row3 onwards); and then click the BUTTON to gather the data again (maybe the delete action could be included as an initial process of the BUTTON?).

Additional info:

• PDF to fit all columns on A4 landscape
• I estimate the imports rows on Sheet1 will not exceed 100 rows (eg no more than 100 worksheets)
• I will rename Sheet1 To Production Schedule
• Each Sheet Name will be in "Purchase Order No.-Product No." format
• Sheets will not be deleted when set to "Complete"
• When a new sheet is inserted, it will be from a custom template (obviously with same key data layout)

Thanks for your time all...


Cheers

TEST Production Schedule1.xlsx

I trust you can place a button from "Form Control" and assign a macro.

Here's a code to get you started. Modify it to suit your needs:

Please Login or Register  to view this content.

Thanks for your efforts jewelsharma!- it is very close to what I desire!

There are some scenarios that are beyond my skills to resolve - would you be able to help me a little further?

The scenarios:

• when a row is copied to Sheet1; cells H4, I4, and J4 are copied with their original formula and lose their reference (therefore they display zero). Can the whole row be copied just as text?
• when the macro reaches the first "Complete" it stops and does not continue searching the remaining sheets in the workbook for "In Production" (refer attached Production Schedule PDF - no info was copied from Sheet5 which was "In Production"). Can this be resolved?
• I attempted to change the sheetnames to the correlating "PURCHASE ORDER No."-this creates an error-refer attached (the sheetname is important for me to find an individual order at a glance)-Can this be resolved?
• the PDF saving function and the date input are FANTASTIC! I'm very pleased with the ease of use!

Thanks for your help...


Trentineer
Production Schedule_03-04-14.pdf
Error message.pdf

Happy to help.

Please rename worksheets as:

• "PURCHASE ORDER No.1"
• "PURCHASE ORDER No.2"
• .... and so on. The names are case-sensitive. The spaces et.al. should be exactly as above.
• Do not rename "Sheet1"

Here's the modified code:

Please Login or Register  to view this content.

Trust this meets your needs. Please mark the thread as [SOLVED].

Regards,
Jewel

Hi Jewel,

Thank you so much!-it works a treat!

My next step is a TOC with hyperlinks to each sheet in column1 and the corresponding unique purchase order numbers in column2...


Cheers,
Trent

Hi Jewel,

I have inserted an INDEX into the workbook to help with navigating to a specific Purchase Order No.

This change has created an error (refer attached).

The Production Schedule is still created, but the save as PDF file function has been lost.


Could you please lend some assistance?


-error message and workbook sample is attached


PS: I am going to post a separate question regarding importing some additional info (column B, C, & D) into the INDEX.



Thanks,
Trent

TEST Production Schedule2-WITH BUTTON.xlsm
Error message.pdf