Macro: Add / Hide / Number Rows Based on Specific Cell Value

[cmcd29]

Hi All,

I'm looking for help on creating a sheet which will generate a blank list based on criteria input in a separate sheet. I have various macros in my sheet which have meant I can do some of what I'm looking for but not all. Separate to this one of my current macros, at times deletes all my data (in Sheet 2), which is very frustrating.

You can find a link to my sheet here: https://www.dropbox.com/s/36372o3wqpt3xul/JRF & Crew List.xlsm?dl=0
Below is a brief explanation of what I have and what I'm looking to get to.

Sheets Explained
Sheet 1: JRF (data input)
Sheet 2: Crew List (data output) with 2 buttons: Generate and Reset
What I'm looking to achieve:

Button 1: Generate (allows user to create a crew list based on data input in Sheet 1)

• hide all rows where the quantity column is zero
• add rows below any where Qty is greater than 1 so that the number of rows = the qty.
  • e.g. Qty = 6; Button will add 5 rows below the original
• number the rows according to the role; start numbering over for each new Position
  • e.g. Supervisor 1, Supervisor 2, Cashier 1, Cashier 2, Cashier 3, Cleaner 1...

​
Button 2: Reset (allows user to reset should the data input in Sheet 1, need changed)

• unhide all rows where the quantity column is zero
• delete any rows that were added using the previous Generate button.

Also not sure if there is a way to auto-generate Sheet 2, based on any time Sheet 1 is changed (and therefore get rid of the buttons entirely) but this would be superb if possible.

I'm sure there's much simpler ways of working this so any help or suggestion would be greatly appreciated as this thing is driving me nuts!

Thanks!
Cassie