Adding Set of Empty Rows to Bottom of Table with Radio Button Macro

[StagHunt]

Hi all,
I hope this message finds you well. I am somewhat new to excel, so bear with me. I haven't really ventured into VBA and am pretty limited to the recorder at the moment but am learning. I think this is an absolute/relative reference mistake somewhere, but I seem to be stuck on it.

I would like my button to add a set of rows (four rows per sample) to the bottom of the table, and then numerically add 1 to the sample number (i.e. add Sample 2, 3, 4... for each time the button is clicked). This will be used as a test template creator to be used with varying test samples sizes (sample count). Would appreciate any help or direction or a video that may nudge me in the right direction. The problem I'm having is with the Sample number not counting correctly after Sample 2.

Sub Macro19()
'
' Macro19 Macro
'

'
Range("B5:U8").Select
Selection.Copy
Range("B9").Select
Selection.Insert Shift:=xlDown
Range("B9:B12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-4]C+1"
Range("B9:B12").Select
Selection.Copy
Range("M9:M12").Select
ActiveSheet.Paste
Range("D5").Select
End Sub

[TMS]

Please see the yellow banner at the top of the page.


Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[GWteB]

Hi & welcome to ExcelForum.

In case your table is called Table1, adding one row to the bottom of your table can be done with this code:

Please Login or Register  to view this content.

In case the bottom row contains any formulas, they will be copied downwards to the newly added row.

[StagHunt]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

OK I think I have what's needed to demonstrate what I am trying to do. I'd like to be able to click radio button X number of times and have X number of samples (2 rows per Sample) added to the bottom of table, while updating the sample number each time a sample is added to the table. So, I'd like to be able to click radio button in "Forces - 1H" 5 times and have Samples 2,3,4,5,6 added to the table. However, my button adds samples 2,2,2,2,2. I think this should be an easy fix but am newer to excel. Thanks.

[GWteB]

See if this is what you're after:

Please Login or Register  to view this content.

[GWteB]

Sorry, forgot to update the sample numbers:

Please Login or Register  to view this content.

[StagHunt]

Thanks, this appears to work.

[StagHunt]

Sorry, forgot to update the sample numbers:

Please Login or Register  to view this content.

Thanks, this appears to work.

[GWteB]

You are welcome and thanks for letting me know.

[StagHunt]

I'm interested in learning more about this code. Say I wanted to Add 4 or 5 or n # of rows per sample instead of 2 as in my example, which part of the code should I be focusing on? or is it not as easy as that? being new to excel, the code seems quite obtuse. I believe I am hung up on the Sample # counter, as I can get everything else to copy using recorder but Sample # does not update. example #2 attached.

[TMS]

I would guess that, wherever you see a 2, you change that to 4, or 5, or n. So, Offset(-2) and Resize(2, 9).

[GWteB]

Due to your additional request, I have changed the approach.
The supplied code assumes that the two tables on the same worksheet have an equal number of rows.
Furthermore, every first column of every table consists of merged cells as in your original case.
Finally, in cell V3 there's a value that indicates the number of desired duplicates.
The code can then be used regardless of the number of rows per Sample.

Please Login or Register  to view this content.