VBA Script To Copy & Paste Values & Formatting To First Empty Row

[cfcMalky]

Hi folks, I'm looking for a VBA script that will copy & paste values & formatting to first empty row,
The range I want to copy will contain values generated by formulas but I want to copy and paste them as values & not formulae,
Also I would like to paste the data with formatting into the first blank row starting from a specific row,

Sheet Name: 'Production Log' (Copy Source & Destination Are Both On This Sheet)
Range To Copy: A4:P4 (Values & Formatting Only)
Paste Location: First blank row from row 9 down (A:P)

I will add a button to trigger the script and label the script 'pasteData'

I've had a working script in the past but lost the sheet I had it on and can't find the forum post that I found it on.

Thanks A Bunch In Advance

 

[DanteAmor]

Try this

Sub copyrow()
    Dim sh As Worksheet, lr As Long
    Application.ScreenUpdating = False
    Set sh = Sheets("Production Log")
    sh.Range("A4:P4").Copy
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
    If lr < 9 Then lr = 9
    sh.Range("A" & lr).PasteSpecial Paste:=xlPasteValues
    sh.Range("A" & lr).PasteSpecial Paste:=xlPasteFormats
    Range("A4").Select
    Application.CutCopyMode = False
End Sub