Macro to duplicate lines N times...

[dpb]

What would a user function look like that if select line, provide a
numeric value and duplicate that line N times?

N of "1" would mean "one additional copy" or two duplicate rows; 2 for
three resulting rows, etc., ...

I've got the keyboard shortcuts that helps but still gets old having to
do this lots of times with a variable count.

Thanks...

--

[Bruno Campanini]

dpb wrote on 23-12-20 :

The new duplicates lines must follow the original one or be added
down to the end of a range?

Bruno

[dpb]

Directly underneath the existing line, Bruno, yes, so they remain as a
group.

I've got to split an award amount amongst multiple accounts when one
fund isn't large enough to make up the total.

--dpb

--

[dpb]

On 12/24/2020 8:38 AM, dpb wrote:
> On 12/24/2020 8:10 AM, Bruno Campanini wrote:
>> dpb wrote on 23-12-20 :
>>> What would a user function look like that if select line, provide a
>>> numeric value and duplicate that line N times?
>>>
>>> N of "1" would mean "one additional copy" or two duplicate rows; 2
>>> for three resulting rows, etc., ...
>>>
>>> I've got the keyboard shortcuts that helps but still gets old having
>>> to do this lots of times with a variable count.
>>>
>>> Thanks...
>>
>> The new duplicates lines must follow the original one or be added
>> down to the end of a range?
>>
>> Bruno
>
> Directly underneath the existing line, Bruno, yes, so they remain as a
> group.
>

The

Ctrl + Spacebar
Ctrl-C
Ctrl Shift + (*)

sequence is a lot quicker than the mouse/right click, etc., but while
the line remains selected, have to recopy and repaste every time; for
some reason Ctrl-Y to duplicate last action is not available.

(*) Took a while to figure out that numeric keypad "+" doesn't work
here, either, only the "+/=" keyboard key for the plus.

--

[Bruno Campanini]

It happens that dpb formulated :

> On 12/24/2020 8:38 AM, dpb wrote:
>> On 12/24/2020 8:10 AM, Bruno Campanini wrote:
>>> dpb wrote on 23-12-20 :
>>>> What would a user function look like that if select line, provide a
>>>> numeric value and duplicate that line N times?
>>>>
>>>> N of "1" would mean "one additional copy" or two duplicate rows; 2 for
>>>> three resulting rows, etc., ...
>>>>
>>>> I've got the keyboard shortcuts that helps but still gets old having to
>>>> do this lots of times with a variable count.
>>>>
>>>> Thanks...
>>>
>>> The new duplicates lines must follow the original one or be added
>>> down to the end of a range?
>>>
>>> Bruno
>>
>> Directly underneath the existing line, Bruno, yes, so they remain as a
>> group.

Select the row you want to duplicate and try this:
=================================
Public Sub AddingDups()
Dim n As Integer, i As Integer, j As Integer

n = 3 ' How many duplicates to add
For j = 1 To n
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
For i = 1 To Selection.Columns.Count
Selection(1, i) = Selection(2, i)
Next
Next

End Sub
================================

Bruno

[dpb]

On 12/24/2020 11:59 AM, Bruno Campanini wrote:
> Public Sub AddingDups()
> Dim n As Integer, i As Integer, j As Integer
>
> n = 3  ' How many duplicates to add
> For j = 1 To n
>    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>    For i = 1 To Selection.Columns.Count
>        Selection(1, i) = Selection(2, i)
>    Next
> Next
>
> End Sub

Well, kinda' close, but not quite right. :)

There's a formula "=ROW()" in one column--I need that for some other
external processing that do on the file. This ends up copying the value
of the existing row the three times instead of duplicating the formula
in the new rows.

Also, not sure how acts on your machine but it takes some considerable
time before it finishes and repaints the screen. It's not a huge
workbook, this one is now about 500 rows with about 30 columns.
Anything magic that can call to turn of updating or the like that might
make it be faster? It's slow enough now would probably just continue
the manual duplication.

I dunno, is it possible to insert the N blank rows and then copy to them
would be quicker? I'm no VBA whiz, just guessing.

I didn't try, I presume one could call Application.InputBox to get the
value for N.

Thanks for the feedback...

--

[Claus Busch]

Hi,

Am Thu, 24 Dec 2020 14:20:18 -0600 schrieb dpb:

> There's a formula "=ROW()" in one column--I need that for some other
> external processing that do on the file. This ends up copying the value
> of the existing row the three times instead of duplicating the formula
> in the new rows.
>
> Also, not sure how acts on your machine but it takes some considerable
> time before it finishes and repaints the screen. It's not a huge
> workbook, this one is now about 500 rows with about 30 columns.
> Anything magic that can call to turn of updating or the like that might
> make it be faster? It's slow enough now would probably just continue
> the manual duplication.

try following code and run the code with right click in the expected
row:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim vardata As Variant
Dim cntCopies As Long

'Modify the columns
vardata = Range("A" & Target.Row & ":AD" & Target.Row)
'Modify the column with the number you want for the copies
cntCopies = Range("A" & Target.Row)

Rows(Target.Row + 1).Resize(cntCopies).Insert
Cells(Target.Row + 1, 1).Resize(cntCopies, UBound(vardata, 2)) = _
vardata
End Sub


Regards
Claus B.
--
Windows10
Office 2016

[dpb]

I'll give this a go, thank you Claus.

Although it dawned on me this would be a spot for macro -- and that does
work quite nicely. I recorded the above keyboard shortcut sequence with
a keyboard shortcut, so that Ctrl-Shift-P (for "Plus") dupes the line.
Then, Ctrl-Y can repeat the macro or repeat it again. Since it is rare
to have to do this more than two or three times (think four is most have
run into so far), that's not bad at all.

Thanks...and MC!

--

[Claus Busch]

Hi,

Am Fri, 25 Dec 2020 07:22:34 -0600 schrieb dpb:

> Although it dawned on me this would be a spot for macro -- and that does
> work quite nicely. I recorded the above keyboard shortcut sequence with
> a keyboard shortcut, so that Ctrl-Shift-P (for "Plus") dupes the line.
> Then, Ctrl-Y can repeat the macro or repeat it again. Since it is rare
> to have to do this more than two or three times (think four is most have
> run into so far), that's not bad at all.

better:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim vardata As Variant
Dim cntCopies As Long

'Modify the columns
vardata = Range("A" & Target.Row & ":AD" & Target.Row)
'Modify the column with the number you want for the copies
cntCopies = Range("A" & Target.Row)

If Target.Count = 16384 Then
Cancel = True

Rows(Target.Row + 1).Resize(cntCopies).Insert
Cells(Target.Row + 1, 1).Resize(cntCopies, UBound(vardata, 2)) = _
vardata

End If
End Sub

and do a right click in the row header of the expected row.

Merry Christmas