I've searched this forum for an answer to my specific issue and couldn't find it. Some of the answered questions were similar to mine but different enough that my limited excel skills couldn't figure out how to modify the solution for my purposes.
My spreadsheet is setup like a form for employees to enter information about guests attending an event. Sheet1 has one column (A) with about 100 rows of information that needs to be supplied in the corresponding cell of column B as applicable. Cell A2 directs the employee to enter the number of guests in the party numerically in cell B2. Cells A20 through A30 are specific to each guest in the party and the first cell in that group serves as a heading called "Guest 1 Info." Cells A31 and below cover other categories of information.
Here's what I'm strugging to accomplish. If there are multiple guests denoted in cell B2, I need rows A20:A30 to be duplicated and inserted below the previous guest's rows (with each new set of rows denoting the applicable guest number (e.g., "Guest 2 Info," "Guest 3 Info," "Guest 4 Info," and so on). As additional rows are inserted, the rows of data below this "guest # info" section will be moved down to accomodate the additional rows.
One of the options I tried was to create macros that would copy and insert rows from another sheet based on the guest quantity. I created a column in Sheet2 that duplicated the A20:A30 cells for up to 20 guests. The macro worked, but I need the cut/paste to be triggered by the number the employee enters in Sheet1 cell B2, rather than manually running the macro. Also, I need the insertions to be dynamic, so that if the employee makes a mistake in B2 and reduces the number of guests (e.g., from 2 guests to 1), the unnecessary rows will be automatically removed.
I'll try to upload a sample workbook that shows the above. Thanks in advance for anyone who can provide a solution.