VBA - Copy last line in area, paste 1 line below, delete formula in above specific row leaving number

[MSchädler]

Hi, I'm racking my brain over this task and maybe someone can help me to find the proper vba-macro to execute this.
Thanks for any help.

Situation.
In row I to N and lines 19 to 36 I input monthly specific numbers. This is my range (I to N, 19 to 36)

Task:
1. I would like a VBA-macro which finds my last line with numbers in that range (I to N), copies that line and paste it into next line below.
2. After that I want to erase the formulae in the above line in row L and replace it with just the number.

Thanks for any help !

 

[MSchädler]

Oh yeah, this is my macro so far and it doesn't work.

Public Sub COPY_MAKRO()
Dim i As Integer
For i = 8 To 13
letzteZeile = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Offset(0, 1).Select
Selection.Copy
With ActiveCell
Range(.Offset(0, 1), .Offset(0, 5)).Select
End With
ActiveSheet.Paste
Next i
End Sub

 

[SQUIDD]

Hi

So i probably do not follow exaclly what you want, but please see below.

This finds last row of data in column I
then copies that row to the row below and changes the existing row to values.

Sub db()
    lr = Range("i" & Rows.Count).End(xlUp).Row
    Range(Cells(lr, 9), Cells(lr, 14)).Copy
    Cells(lr, 9).PasteSpecial xlValues
    Cells(lr + 1, 9).PasteSpecial xlValues
End Sub

 

[MSchädler]

Hi

So i probably do not follow exaclly what you want, but please see below.

This finds last row of data in column I
then copies that row to the row below and changes the existing row to values.

Sub db()
    lr = Range("i" & Rows.Count).End(xlUp).Row
    Range(Cells(lr, 9), Cells(lr, 14)).Copy
    Cells(lr, 9).PasteSpecial xlValues
    Cells(lr + 1, 9).PasteSpecial xlValues
End Sub

Hello Squidd

Thank you for your quick help. Your VBA works well. Now I have to further expand it to fit my needs.

Thanks again

 

[MSchädler]

Hello Squidd

Thank you for your quick help. Your VBA works well. Now I have to further expand it to fit my needs.

Thanks again

Hello Squidd
Can you help me with the following problem:
I have used your VBA in my excel sheet and the main task works well. I thought I could now refine that VBA but I'm coming to my limits.
This is the adapted VBA:

Sub START()
lr = Range("i" & Rows.Count).End(xlUp).Row ' find last row of data
Range(Cells(lr, 9), Cells(lr, 14)).Copy ' copy the last row

Cells(lr + 1, 9).PasteSpecial xlFormulas ' paste 1 row below with Formulas


Range(Cells(lr, 11), Cells(lr, 11)).Copy ' copy one cell with formulas HERE I WANT TO COPY SPECIFIC CELL (with formulas) FROM THE PREVIOUS ROW
Cells(lr, 11).PasteSpecial x1Values ' paste into same cell as Value AND PASTE IT AS A VALUE (no formulas)


End Sub

Can you help?
Thank you in advance

 

[SQUIDD]

Hi

Thanks for the feedback, not sure if this is what you wanted, but you should be able to adjust it to suit

Sub db()
    lr = Range("k" & Rows.Count).End(xlUp).Row 'gets the last row of data in column K
    Range("i" & lr & ":n" & lr).Copy 'copies i to n of said last row
    Range("i" & lr + 1).PasteSpecial xlValues 'pastes the copied range to the row below as values.
    Range("K" & lr).AutoFill Destination:=Range("K" & lr & ":K" & lr + 1), Type:=xlFillDefault 'drags the formula from column k down 1 row from last row
    Range("i" & lr & ":n" & lr).Copy 'copies the lasrow of data again
    Range("i" & lr).PasteSpecial xlValues 'pastes them back to the same range as values
End Sub

 

[MSchädler]

Hi Squidd
thanks again for your help.
That VBA works quite well and does what I was looking for. Now just a bit of tweeking
Have a great day
Marc

 

[SQUIDD]

very welcome