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

 

[deeptech04]

Below is the screenshot , hope you can see it, as I am in office, cannot use the tools you mentioned in the faq page (PC's in office don't have install permission)

Hoe you can see the image, now suppose I want to copy the selected section, it will copy to end of all data as shown in (highlighted) yellow in same format.

I made the cell highlight to make you understand only.

 

[Joe4]

No, I cannot see your image. I cannot tell if it is because it isn't working, or because the image is being blocked by my workplace security.

In any event, I think I get the gist of what you are trying to do. Try this:

Sub MyCopy2()

    Dim c As Long
    Dim numRows As Long
    Dim numCopies As Variant
    Dim rng As Range
    Dim startCol As Long
    Dim startRow As Long
    
'   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

'   Set range to copy as selected cells
    Set rng = Selection
   
'   Find the number of rows in the selected range and the starting column
    numRows = rng.Rows.Count
    startCol = rng(1, 1).Column
    
'   Set intial value of start row (by finding last row with data in column A)
    startRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
    
'   Loop through number of copies
    For c = 1 To numCopies
'       Copy data
        rng.Copy Cells(startRow, startCol)
'       Calculate next start row
        startRow = startRow + numRows + 1
    Next c
    
    MsgBox "Copy complete!", vbOKOnly
        
End Sub

It will copy your selected range the number of times you enter at the prompt.

 

[deeptech04]

No, I cannot see your image. I cannot tell if it is because it isn't working, or because the image is being blocked by my workplace security.

In any event, I think I get the gist of what you are trying to do. Try this:

Sub MyCopy2()

    Dim c As Long
    Dim numRows As Long
    Dim numCopies As Variant
    Dim rng As Range
    Dim startCol As Long
    Dim startRow As Long
    
'   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

'   Set range to copy as selected cells
    Set rng = Selection
   
'   Find the number of rows in the selected range and the starting column
    numRows = rng.Rows.Count
    startCol = rng(1, 1).Column
    
'   Set intial value of start row (by finding last row with data in column A)
    startRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
    
'   Loop through number of copies
    For c = 1 To numCopies
'       Copy data
        rng.Copy Cells(startRow, startCol)
'       Calculate next start row
        startRow = startRow + numRows + 1
    Next c
    
    MsgBox "Copy complete!", vbOKOnly
        
End Sub

It will copy your selected range the number of times you enter at the prompt.

Your code is really superb, and you thought correctly and coded perfectly as I mentioned above and provided in the image.

Thanks a lot again man.. Really appreciate your fast support.

If you don't mind, can I ask what job you do ? I am curious seeing your fast and brilliant coding skills.

 

[Joe4]

You are welcome! Glad I was able to help.

My job has evolved over the years. I started out doing adminstration, then part of software conversion/implementation teams, then process improvement (this is where I really learned Excel VBA and Access), and then computer programming (more or less) since then. So I have done a bit with VBA, SQL, and now dabbling in Java.

 

[deeptech04]

You are welcome! Glad I was able to help.

My job has evolved over the years. I started out doing adminstration, then part of software conversion/implementation teams, then process improvement (this is where I really learned Excel VBA and Access), and then computer programming (more or less) since then. So I have done a bit with VBA, SQL, and now dabbling in Java.

Nice to hear that bro, you are experienced in many fields and also good at them, It was my pleasure to get acquainted with you and this forum.

 

[Joe4]

Thanks for the kind words.
Being a "problem-solver", I find this forum a lot of fun and enjoy working of these type of challenges, and helping to teach people about Excel and Access.