Duplicating Selected Cells in Excel

[deeptech04]

Can anyone help me in this, tried to find a lot but couldn't.

I need a macro to duplicate few selected cells (rows and columns) to be duplicated for x no of times keeping a row gap after each duplicate.

Similar to the link below but not single row or column , needs to be for selected cells.

Example :

No of Times you want to duplicate : 2

Data 1
Data 2
Data 3

Data 1
Data 2
Data 3

Data 1
Data 2
Data 3


Please help

 

[Joe4]

Welcome to the Board!

Is your data contiguous (no totally blank rows or columns in the middle of the data)?
Where does the data start (in cell A1, maybe)?
For rows with data, will column A always have a value?

 

[deeptech04]

Is your data contiguous (no totally blank rows or columns in the middle of the data)?

Yes data is contiguous with blank rows in B, but I need 1 blank row after each duplicate.

Where does the data start (in cell A1, maybe)?

Actually data is starting from A2, A1 is header and the values needed to be duplicate is multiple row and columns.

For rows with data, will column A always have a value?

Yes, Column A always has a value but column B may or may not have value.

Example screenshot attached. (Need to Duplicate A and B rows x no of times depending need and a row gap after each dublicate and not E,F,G,H,I,J,K)

 

[Joe4]

I do not see any screen shot. Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

So, will you always want to copy JUST columns A and B?
You said not to copy columns E-K. What about columns C and D?
Would you like to be prompted for how many copies to request?

 

[Joe4]

If we are always just copying columns A and B, here is some code that will prompt you for the number of copies.

Sub MyCopy()

    Dim lastRow As Long
    Dim rng As Range
    Dim numCopies As Variant
    Dim c As Long
    Dim startRow As Long
    
'   Find last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Determine number of rows to copy and set range to copy
    If lastRow > 1 Then
         Set rng = Range("A2:B" & lastRow)
    Else
        MsgBox "No data to copy", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Prompt for number of copies
    numCopies = InputBox("How many copies would you like")
    
'   Verify entry
    If (Not IsNumeric(numCopies)) Or (numCopies < 1) Then
        MsgBox "You have not entered a valid number of copies", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Copy data
    For c = 1 To numCopies
'       Calculate start row
        startRow = lastRow * c + 2
        rng.Copy Cells(startRow, "A")
    Next c
    
    MsgBox "Copy complete!", vbOKOnly
        
End Sub

 

[deeptech04]

If we are always just copying columns A and B, here is some code that will prompt you for the number of copies.

Sub MyCopy()

    Dim lastRow As Long
    Dim rng As Range
    Dim numCopies As Variant
    Dim c As Long
    Dim startRow As Long
    
'   Find last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Determine number of rows to copy and set range to copy
    If lastRow > 1 Then
         Set rng = Range("A2:B" & lastRow)
    Else
        MsgBox "No data to copy", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Prompt for number of copies
    numCopies = InputBox("How many copies would you like")
    
'   Verify entry
    If (Not IsNumeric(numCopies)) Or (numCopies < 1) Then
        MsgBox "You have not entered a valid number of copies", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Copy data
    For c = 1 To numCopies
'       Calculate start row
        startRow = lastRow * c + 2
        rng.Copy Cells(startRow, "A")
    Next c
    
    MsgBox "Copy complete!", vbOKOnly
        
End Sub

Thanks a lot, worked like a charm!

but as you said it will copy only Row A and Row B , therefore asking is it possible to tweak the same code you provided but with selected cells only.

I mean, same work your code is doing but the values will be the selected cells (it maybe be multiple rows and multiple columns as selected, continuous).

 

[Joe4]

Will you ever be selecting anything less than all rows of data?
For example, let's say that there is data in column A down to row 10.
Will you ever be selecting/copying data that doesn't go all the way down to row 10?
And if so, will we be copying the selected range to row 12, and down from there, or will we be inserting these copies in the middle of the data?

It will affect how much I need to change the code.

 

[deeptech04]

Sorry to trouble you, also asking you to make the same code with selected cells because if there is other data on top of A/B it will duplicate those also and
2nd also with the above code I am bind to A and B rows , I was asking something flexible so that I can select a section I want to duplicate below it.

Like as you said only A and B rows will work in the above, suppose I like to duplicate A1 till E5 then this code wont work, therefore asked a solution.

It maybe selection based or manually entering the From and To cell numbers like that.

Hope I am not annoying you, just wanted bit dynamic working code..


Thanks a lot for helping so quickly.. Thanks again, and sorry, as I was in office most of the file-sharing site is blocked therefore posted the image like that.

 

[Joe4]

Please see my previous post (it looks like we posted at the same time).
I have a few follow-up questions I need answered in order to determine how to alter the code.

 

[deeptech04]

Will you ever be selecting anything less than all rows of data?
For example, let's say that there is data in column A down to row 10.
Will you ever be selecting/copying data that doesn't go all the way down to row 10?
And if so, will we be copying the selected range to row 12, and down from there, or will we be inserting these copies in the middle of the data?

It will affect how much I need to change the code.

Selected data will be continuous , like If I am selecting from B5 to G10 it will be continuous section and no data will be left/skipped in between that.
and yes if the sheet has data from A1 to G20 and I select only as I said B5 to G10 , then I want the data to be posted/repeated from B22 onward (at the end of all data, not in between).

I will try to post screenshot within few secs.