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!
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:
