VB Code or macro to insert 32 new rows with data and formulas

[kmham]

Hello all and just so you know I'm not real good with VB code so please bear with me (also be specific about exactly how I can make this work).

I'm working with Excel 2010

Tab name that I'm working with is called "Data"

It's 6 columns wide with column headings called: CYCLE #, RTE #, BOUNDARY, DAYS BETWEEN, CYCLE START DATE, CYCLE FINISH DATE

Row 1 has the column headings.
Data starts in Row 2
Currently I have data down to Row 65

So what I'm looking for is (I'm assuming I'll need VB Code) to push a button and run code that will:
1. First, the automatic code will need to determine that Row 65 is currently my last row containing Data (this can be based on looking at Column A) - formula COUNTA(A:A) yields a value of '65' which matches the last row that currently contains data.
2. Next this automatic code will need to know the current MAX value in column A. Currently Rows 2-33 all have a value of '1' and Rows 34-65 all have a value of '2'. So the Max Value of Column A (not including the column heading "CYCLE #) is '2'.
3. Everything is done in groups of 32 (hence why the data currently ends on Row 65 (1 column heading in row 1, then 32 rows of data, then another 32 rows of data)
4. Next, we need to insert 32 NEW RECORDS where...
a. Column A, gets a value of '3' which is the next numerical value after '2' which was determined in step 2 above (so Rows 66-97 in Column A would all be auto-populated with a '3')
b. Column B always gets populated with the same identical prior 32 rows (i.e. Cells B34:B65 simply get replicated into Cells B66:B97)
c. Column C always gets populated with the same identical prior 32 rows (i.e. Cells C34:C65 simply get replicated into Cells C66:C97)
d. Column D contains a formula (ex. Cell D34's formula is =IF(F34="","",F34-F2) , Cell D35's formula is =IF(F35="","",F35-F3) and so on). Similar to Columns B & C, the last 32 rows in Column D need replicated into the next 32 rows (i.e. Cells D66:D97), so cell D66 will contain the formula =IF(F66="","",F66-F34) and end in cell D97 with formula =IF(F97="","",F97-F65)

Next time user wants to insert another 32 rows, the auto code would know that D97 is now the last row so next time "button" is pushed and a new 32 records are created in Cells A98:D129.

Once this code is in place I believe I know how to connect it to a button (like a Macro) unless VB Code doesn't work the same way. But I thought I would mention that this is what I'm ultimately going to do. So when the button is pushed, I'd also like to prompt the user with a pop-up message that says "You're about to insert 32 new records. Would you like to continue?" Yes / No

If Yes, then finish the VB Code and provide message that says "Successful - 32 new records have been created and are ready for use" OK (click okay to close)
If No, provide message that says "Process cancelled - no records were inserted" OK (click okay to close)

Many thanks in advance for anyone that can assist with making this work for me!

[vichopalacios]

Hi @kmham

Please check the attached file
Comment us if it is what you asked for.

Vicho

[kmham]

WORKED PERFECTLY! You're a life saver. Thank you very much!

Hi @kmham

Please check the attached file
Comment us if it is what you asked for.

Vicho

[kmham]

vichopalacios... hoping you're online

I had to make a modification. Columns A-F haven't changed. Columns A-D add 32 new rows beautifully. But I had to add Column G which contains this formula (=IF(F1729="","No",IF(AND(E1729>=Date1,Data!E1729<=Date2),"Yes","No")) - where Date1 and Date2 are cell references from another tab. Anyway, what change to the VB Code would I need to insert the 32 new rows for columns A-D and G?

[kmham]

Sorry, never mind...I just moved Column G to be Column D and then I updated this line of code so it now copies 5 columns instead of 4...
.Range(.Cells(FirstRow - 32, 2), .Cells(FirstRow - 1, 4)).Copy _

[vichopalacios]

Great.
So glad to now you are managing well with it.

Any time you can post over here, and/or send a PM

VICHO