How I can copy the entire last row to the next row?

[Hernan_g_f]

Dear all,

I need to copy the last row of a matrix to the next row. How can I do it?


I wrote the following sentence:

Sub M16b()


Dim wb As Workbook
Set wb = Workbooks("SCDJ Excel VBA.xlsm")

Dim sh As Worksheet
Set sh = wb.Sheets("16b")

Dim rng1 As Range
Set rng1 = sh.Range("B17")

'---------

'2. Insert the last row


rng1.End(xlDown).Select
ActiveCell.EntireRow.Select
Selection.Copy

==> 'I need to copy the last row to the next row (down)

Selection.Paste '(¿?)


End Sub


Thanks in advance!!

 

[kevin9999]

I need to copy the last row to the next row (down)

So you'll end up with the last row twice (duplicated) - is that what you're after?

 

[Hernan_g_f]

So you'll end up with the last row twice (duplicated) - is that what you're after?

Yes Kevin, exactly!!!

 

[kevin9999]

OK, see if the following gives you what you want:

Option Explicit
Sub Hernan()
    Dim ws As Worksheet
    Set ws = Workbooks("SCDJ Excel VBA.xlsm").Worksheets("16B")
    Dim LRow As Long, LCol As Long, r As Range
    LRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
    LCol = ws.Rows("17:" & LRow).Find("*", , xlFormulas, , 2, 2).Column
    
    Set r = ws.Range(ws.Cells(LRow, 2), ws.Cells(LRow, LCol))
    r.Offset(1).Value2 = r.Value2
End Sub

 

[Hernan_g_f]

Hi Kevin, yes, very helpful, but it's biased. I need that when duplicating the last row, the formulas are also copied

I need to copy the formulas that are into the matrix

Column => A B C

Rows

1 +x1*x1 +x2*x2 +x1*x3
2 + x2*x1 +x2*x2 +x2*x3
3 + x3*x1 +x3*x2 +x3*x

With the code that you wrote me, I can duplicate the row 3, but when I run your VBA code, the new row (4) duplicate the numbers of the third row, but not the formulas

How can I do it?

Thanks in Advance!!!

 

[kevin9999]

Try this one instead (much easier if you could post a copy of your sheet using the XL2BB - Excel Range to BBCode)

Option Explicit
Sub henan_V2()
    Dim ws As Worksheet
    Set ws = Workbooks("SCDJ Excel VBA.xlsm").Worksheets("16B")
    Dim LRow As Long, LCol As Long, r As Range
    LRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
    LCol = ws.Rows("17:" & LRow).Find("*", , xlFormulas, , 2, 2).Column
    
    Set r = ws.Range(ws.Cells(LRow, 1), ws.Cells(LRow, LCol))
    r.Copy r.Offset(1)
End Sub

 

[Hernan_g_f]

Thank you very much Kevin!!! Best wishes! Hernán!!!!!!!!!!

 

[kevin9999]

Happy to help, and thanks for the feedback