copy rows in excel multiple times

[cip888]

Hi All

I have data in rows in one column(A), that I would like to paste onto another sheet n times in one column(X) separated by one row.

For example:

n=2

column A column X
A A
B A
C
B
B

C
C

Does anyone know how to setup a macro to achieve that?

Any help would be much appreciated, thanks in advance !

 

[Harry Flashman]

Try this macro

Option Explicit

Sub CopyValuesMultipleTimes()
Dim rng As Range, rngCell As Range
Set rng = Selection

Dim x As Long
x = InputBox("How many times do you want to repeat each value?")

Dim wsDest As Worksheet
Set wsDest = Worksheets.Add(After:=ActiveSheet)
wsDest.Name = "Destination"

Dim rngDest As Range

Dim i As Long
For Each rngCell In rng
    If rngCell = rng(1) Then
        wsDest.Range(Cells(1, 1), Cells(x, 1)).Value = rngCell
    Else
        For i = 1 To x
            With wsDest
                If i = 1 Then
                    Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(2, 0)
                Else
                    Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                    End If
            End With
            rngDest = rngCell
        Next i
    End If
Next rngCell
End Sub

You will need to select the values you want to copy (my macro assumes that all the values are in one column).
The macro will insert a new worksheet called "Destination".
The macro will ask you how many times you would like to duplicate each value.
The macro will write each value multiple times to a new worksheet, inserting a rows as it goes.
I hope this helps.

 

[cip888]

Try this macro

Option Explicit

Sub CopyValuesMultipleTimes()
Dim rng As Range, rngCell As Range
Set rng = Selection

Dim x As Long
x = InputBox("How many times do you want to repeat each value?")

Dim wsDest As Worksheet
Set wsDest = Worksheets.Add(After:=ActiveSheet)
wsDest.Name = "Destination"

Dim rngDest As Range

Dim i As Long
For Each rngCell In rng
    If rngCell = rng(1) Then
        wsDest.Range(Cells(1, 1), Cells(x, 1)).Value = rngCell
    Else
        For i = 1 To x
            With wsDest
                If i = 1 Then
                    Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(2, 0)
                Else
                    Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                    End If
            End With
            rngDest = rngCell
        Next i
    End If
Next rngCell
End Sub

You will need to select the values you want to copy (my macro assumes that all the values are in one column).
The macro will insert a new worksheet called "Destination".
The macro will ask you how many times you would like to duplicate each value.
The macro will write each value multiple times to a new worksheet, inserting a rows as it goes.
I hope this helps.

Hi Harry thank you for your prompt reply
.
I have tried your macro but after I am asked "how many times do you want to repeat each value?" i get a Run-time error '1004': Application-defined or object-defined error.

Also I noted that my questions was not very clear, I repost the last part here below:

For example:

n=2

column A
A
B
C

column X should be
A
A

B
B

C
C

 

[Harry Flashman]

Hi Harry thank you for your prompt reply
.
I have tried your macro but after I am asked "how many times do you want to repeat each value?" i get a Run-time error '1004': Application-defined or object-defined error.

Also I noted that my questions was not very clear, I repost the last part here below:

For example:

n=2

column A
A
B
C

column X should be
A
A

B
B

C
C

That is exactly what the macro does. I am not sure why you are getting that error. I have tested this on both Excel 2010 and Excel 2013 and it runs fine.
See the following video for an illustration of how it runs (you can watch the video in your browser).

CopyValuesNTimes - aflashman's library

Please note that it assumes your values are in one column. Select the range of values (not the entire column). Make sure that what you are doing is similar to the way I run the macro in the video.

If you are entering the number requested by the input box via the number pad make sure you have NUM LOCK turned on otherwise you won't be entering a number.

 

[cip888]

Thanks Harry

I followed your video and now the macro is working beautifully !! I think it might had something to do with the fact that I saved the macro on one sheet rather than in the ThisWorkbook object

 

[Harry Flashman]

Thanks Harry

I followed your video and now the macro is working beautifully !! I think it might had something to do with the fact that I saved the macro on one sheet rather than in the ThisWorkbook object

The appropriate place to put such a macro is in a module.
From the VBE choose Insert/Module

InsertVBAModule - aflashman's library

 

[cip888]

Great Thanks, really appreciated !!

 

[Harry Flashman]

Great Thanks, really appreciated !!

No worries. Glad I could help.