VBA: Would like to grab Current Row, Paste as Formula/Format in blank line below, and then also paste overitself as values

[BGAA2011]

Good afternoon Gurus! So, I have the code built to copy and paste the formula and formatting to the blank line below it, however I cannot for the life of me figure out how to then make the recently-copied line values so the formula is only ever on the bottom most line.

It is a historic weekly tracker, so I need the values each friday, so I want the bottom line to be the only one containing formulas, and the lines above it to all just be the values of the cells.

Here is what I have so far which works perfectly to copy the formulas and formats, just need a way to get the copied range pasted as values over itself and then as formulas and values directly below it.

Sub PrepareNextweek()
Dim WS_WeeklyMetrics As Worksheet
Set WS_WeeklyMetrics = Sheets("Weekly Metrics")

TodayDate = Date

'Turn off flickering of screen while macro runs
Application.ScreenUpdating = False

'Copy/Paste Old Data
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Weekly Metrics")
Set pasteSheet = Worksheets("Weekly Metrics")
WS_WeeklyMetrics.Select
copySheet.Range("A5:N5").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[Fluff]

Do you want A5:N5 to be values, or all rows below that (with the exception of the last row) to be values?

 

[BGAA2011]

So I need to run this every friday. For example, the first week (this week) the formulas are in A5:N5. Next friday when I hit the "run report" button, I want to copy A5:N5, paste over itself as values so the forumlas are gone and the data stays, and then paste the formulas and formatting to the row below it.

Then two weeks from now grab A6:N6 and so forth.

I have the formula working perfectly for this except for pasting the values part.

 

[Fluff]

Try

Sub PrepareNextweek()
    
    'Turn off flickering of screen while macro runs
Application.ScreenUpdating = False
    
    'Copy/Paste Old Data
    With Worksheets("Weekly Metrics")
        With .Range("A" & Rows.Count).End(xlUp).Resize(, 14)
            .Copy
            .Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
            .Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
            .Value = .Value
        End With
    End With
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[BGAA2011]

Flawless! Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback