Excel 2007 simple copy and paste vba not working in excel 2016

Question

I've put together what is an audit workbook which is supposed to copy and paste some hidden cells to a data storage sheet that is hidden the collective data in the hidden sheet is then used for KPI reports.

The problem is that I've put it together in Excel 2007, in which it works perfectly but now it needs to be used in Excel 2016.

I'm running the macro from a button, if the button is pressed it copies the data and selects the field in the hidden sheet but doesn't paste, no error dialogues either.

When I step through the code in the vba editor it works fine, but not from the button despite the button being linked to the code. I'm a bit lost or what to do as I don't know how to correct the code.

Here's the code:

Sub CopyPhoneAuditToDB()
'
'
' Copy Phone Audit To DB

'
    Application.ScreenUpdating = False
    Sheets("Data Phone").Visible = xlSheetVisible
    Sheets("Data Phone").Unprotect
    Range("E49:T49").Select
    Selection.Copy
    Sheets("Data Phone").Select
    Range("A2").Select
    Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Protect
    Sheets("Phone Audit").Select
    Range("H5:J5").Select
    Sheets("Data Phone").Visible = xlSheetVeryHidden
    ActiveWorkbook.Save
End Sub

Answer 1

Please let me know if this works for you:

Option Explicit

Sub CopyPhoneAuditToDB()

Application.ScreenUpdating = False

With Sheets("Data Phone")
    .Visible = xlSheetVeryHidden
    .Unprotect
    Sheets("Phone Audit").Range("E49:T49").Copy
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    .Protect
End With

With Sheets("Phone Audit")
    .Activate
    .Range("H5:J5").Select
End With

Application.ScreenUpdating = True
ActiveWorkbook.Save

End Sub