Duplicate rows with data and paste bellow - VBA

Question

I have a VBA code to add lines, it works well on my table, but im now trying to make a new code to get that data of an entire row, and paste it bellow the "original" row, my code so far

Sub INSERT_ROW_CC()

Application.ScreenUpdating = False
Application.CutCopyMode = False

    Dim Table As Object
    Dim Rows As Range
    Set Rows = Worksheets("CC").Range("B18")
    Dim Rng As Range
    Set Rng = ActiveCell
    Dim Data As String
    Set Data = Rng.EntireRow
    
Set Table = ActiveSheet.ListObjects(1)
With Table
    If Not Intersect(Selection, .DataBodyRange) Is Nothing Then
        Rng.EntireRow.Offset(1).Resize(Rows.Value - 1).Value(Data).Insert Shift:=xlDown
    End If
End With

Application.ScreenUpdating = True
Application.CutCopyMode = True

End Sub

The number of rows to be inserted is taken from behind another sheet, it is subtracted by 1 to compensate that I will always have 1 row that I want to duplicate. I'm not able to reference the "mother" row as the data or string to be pasted when the code inserts the additional rows. I'm using this code in a formatted table, so i dont wanna break my conditional formating, it doesn't have any formula in it, it's like a table of things to buy or do, all the data I enter manually or copy from other sources, pasting only values.

Set Rows = Worksheets("CC").Range("B18")

This will tell me how many lines I should have, the code I had created before this one worked perfectly without copying the contents of the line above where the other lines were added. If the number of rows is 1-1 i want to exit this sub, and will do something like a msgbox

Most of the times i will have a table filter at the moment i am adding the rows.

This table of mine is full of content, the fact that I want this code to work is what will make it easier for me to have another code that throw some contents to clipboard to work effectively, so I intend to merge them.

I'm stuck in this code at the moment, adding the lines was even easy, a content and pasting in the added lines is taking work.

Current Cell

Inserting Rows (Let's say the code is telling me that I have to have 5-1 rows)

Intended Result

Sorry the question was too long, I tried to add as much information as possible, thanks in advance!

Does this answer your question? Excel VBA to copy multiple rows and insert to next row depending on button click — bonCodigo
– bonCodigo, Commented May 3, 2023 at 16:30

steveP · Accepted Answer · 2022-09-26 15:25:33Z

0

This might help.

This code will insert the relevant amount of rows specified in column 27(AA) and copy columns A:Z from the original row into the inserted rows.

Sub InsertRowsAndCopyDown()

Dim wb As Workbook
Dim ws As Worksheet
Dim r As Long

Set wb = ThisWorkbook
Set ws = Sheets("My Sheet")

ws.Activate

For r = ws.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
With Cells(r, 27) 'Column 27 should contain how many rows you want inserted 
below original row.
If (.Value) > 0 Then
  If IsNumeric(.Value) And Not IsEmpty(.Value) Then
    Rows(r + 1).Resize(.Value).Insert
    Range(Replace("A#:Z#", "#", r)).Copy
    Range("A" & r + 1).Resize(.Value).PasteSpecial Paste:=xlPasteValues
            
  End If
 End If
End With
Next r

Application.CutCopyMode = False

End Sub

answered Sep 26, 2022 at 15:25

steveP

1097 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cooper Over a year ago

With Cells(r, 27) it should be column AA1 right? tried your code, but your code is not respecting the limits of the table, and is not pasting just below the activecell, must I be doing something wrong? I just modified the Set ws = Sheets("My Sheet") to ActiveSheet

Collectives™ on Stack Overflow

Duplicate rows with data and paste bellow - VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related