Private Sub CommandButton1_Click()
    
    Worksheets("Sheet1").Unprotect Password:="#######"

    Dim currentRow As Integer
    Dim currentCol As Integer
    Dim tpnbNew As Integer
    
    'tpnbNew = InputBox("Please enter your new TPNB")
       
    ' where is the cursor
    currentRow = ActiveCell.Row
    currentCol = ActiveCell.Column
    
    ' insert now row above current row
    Rows(currentRow & ":" & currentRow).Select
    Selection.Insert Shift:=xlDown
    
    ' select old row (since currentRow was shifted down one this is now currentRow+1, select to end and copy
    Range("A" & currentRow + 1, "IV" & currentRow + 1).Select
    Selection.Copy
    
    ' select first cell in dest row and paste
    Range("A" & currentRow).Select
    ActiveSheet.Paste
    
    ' restore cursor to correct column in new row
    Cells(currentRow, currentCol).Select
    
    Worksheets("Sheet1").Protect Password:="######"

End Sub

Rows(currentRow+1 & ":" & currentRow+1).Select

that does make sence :/

I've also managed to get it to only copy the cells which have a formula in it, and leave the other cells blank.

Just need to figure how to get the value of a prompt into the certain cell, but could be tricky, as the code now asks how many news rows a want to insert! :B

do you know haw to make the worksheet not refresh (or prompt to refresh) external links on startup?

The sheet only needs refreshing once a week, and only by me, and dont want others being prompted to do so!

thanks