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