how to use a value in a cell from one worksheet to trigger insertion/removal of a range of cells from another worksheet


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.



I would also make a copy of your MISC data on another sheet.
I would then use the code sheet attached to the worksheet 
Use the built in worksheet change to get the value from you number of attenties.
Clear all the data from your guest info to the bottom.
From the number calculate the last row to copied from sheet 2.
and you shopul the know where to put the misc info
so copy and paste that there.
As each time you update the number of attendies all the attendie and misc info is cleared and you will have the correct number of forms to be completed.

k1w1sm (rep: 122) Aug 13, '19 at 9:09 pm
Thanks for the feedback, k1w1sm.
Blu (rep: 2) Aug 14, '19 at 2:15 pm
Add to Discussion


Selected Answer

Your idea was realised in the attached workbook. To install the code in your own project copy everything from the code sheet of Sheet2 to the code sheet of the worksheet on which you want the action. The correct location is important.

The code will run as the number in cell B2 changes. It's very fast.

The enumeration of values below enables you to change the address of the trigger cell as well as other parameters. Just change the numbers as required. You find this enumeration at the top of the code sheet.

Private Enum Nws                        ' worksheet navigation
    ' 14 Aug 2019

    ' you may change the assigned values to match your worksheet
    NwsCaptionColumn = 1                ' 1 = column A
    NwsDataColumn = 2                   ' 2 = B
    NwsGuestCountRow = 2                ' Cells(NwsGuestCountRow, NwsDataColumn) = Trigger cell
    NwsFirstGuestRow = 20
    NwsGuestDataCount = 11              ' number of data collected for each guest
                                        ' When changing the GuestDataCount manually reset all existing
                                        ' guests to have the new number of data rows
End Enum

Edit 18 Aug 2019 ===========================================

The attached workbook dated August 18 has all the previous functionality but reacts, in addition, to a change in the first row of each set of guest data. It converts the name entered there to proper case, fills the next line's data with an upper case version of that same name and selects the field below that.



Thx for this fantastic solution, Variatus!  This is very helpful.  I'm tweaking it to customize to my needs and to try to better understand how you did this.  I have some quick follow up questions.  Can your Template sheet be deleted?  It doesn't appear to be doing anything in the VBA code.  Also, B22 uses the UPPER formula to convert the Guest's name to all caps.  How can I preserve/repeat that formula in any additional Guest rows that get inserted?  Finally, once this form is completed by the employee, is there a foolproof and efficient way to convert all data from rows to columns while preserving the formulas so I can use the form in a mail merge with Word?  I know how to do that manually with Copy>Paste Special>Transpose, but that presents some undesirable challenges. 
Blu (rep: 2) Aug 14, '19 at 2:14 pm
You had named Sheet1 as "Sheet2" and Sheet2 as "Sheet1". So I streamlined the naming. Yes, the sheet ending up with the name "Template" has no function in my solution and can be deleted.
I dislike using VBA to write worksheet functions. It's a prejudice I carry with me and what you request could be done. I just think that the alternative I programmed and added to my answer today has some advantages. Please take a look.
As for follow-up questions in general, I don't do them. And when they demand complicated and voluminous answers like the one you thought up I am really and honestly glad that I don't :-). I suggest that you try your luck with a new question. 
Variatus (rep: 2938) Aug 18, '19 at 8:23 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login