Insert copied row at end of specific table, then copy entire table

mdm963

New Member
Joined
Jan 5, 2014
Messages
1
I'll try to explain my scenario...
I'm trying to make a guest list for parties, and I want to have these guest lists on a single worksheet. I want to have 2 macros that do 2 things:

1) Prompts to ask how many guests (and then copies the 4th row of the list (row 5) and pastes x times before the last row of the list.

2) Copies the first 4 rows and the last row of the previous guest list (In this case rows 2-5, and row 6 without the added guests that would be inserted) and pastes them below the previous list (leaving a couple blank rows between. Of course, ultimately the first macro should work with this new list as well.

Is something like this possible? Also thinking about how to initiate the macro, by clicking on a specific cell or something. Not sure how this would copy down when macro 2 is run.

I have 2 macros that I got from other forums that are somewhat close to making macro 1.

1) this one prompts and copies/pastes, but only pastes the row selected exactly below it.



<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;"> Sub test2()Dim n As Integer, rng As Range 'new section >> On Error GoTo EH Set rng = Application.InputBox("Select any cell/cells within range to copy", Type:=8) '<<---rng.Selectline2:n = InputBox("type no. of times you want to be repeated minus 1 for e.g if you wnat to be repeated 3 times type 2")Range(rng.Offset(1, 0), rng.Offset(n, 0)).EntireRow.InsertRange(rng, rng.End(xlToRight)).CopyRange(rng, rng.Offset(n, 0)).PasteSpecial'Selection code:Rng.offset(n,0).select 'this section is not necessary>> 'Set rng = rng.Offset(n + 1, 0) 'If rng = "" Then 'GoTo line1 'Else 'GoTo line2 'End Ifline1:Application.CutCopyMode = False 'range("a1").Select 'i don't think you need itMsgBox "macro over" 'Stop is not needeExit SubEH: MsgBox "Sub interrupted"End Sub</code></pre>
2) This one copies the last row of the worksheet and pastes it below the last (not second to last) with a prompt with how many:

Sub test()
Dim HowMany As Variant
HowMany = InputBox("Enter total number of guests", , 1)
If HowMany = "" Then Exit Sub
With Range("A" & Rows.Count).End(xlUp)
.EntireRow.Copy .Offset(1).Resize(HowMany - 1)
End With
End Sub





I appreciate all your help, wish I could be a genius like you guys!
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,147,743
Messages
5,742,935
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top