CODEWITHSUNDEEP
excelexcel-2007excel-2016vba
John Murrey
John Murrey

Reputation: 41

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

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

Upvotes: 1

Views: 698

Answers (2)

John Murrey
John Murrey

Reputation: 41

I found the issue, where Sheets("Data Phone").Visible = xlSheetVisible Sheets("Data Phone").Unprotect were taking focus away from the sheet it was run from Phone Audit it was more than likely copying E49:T49 from the Data Phone sheet.

See the following code that includes a new line to bring focus back to the Phone Audit sheet.

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

'
    Application.ScreenUpdating = False
    Sheets("Data Phone").Visible = xlSheetVisible
    Sheets("Data Phone").Unprotect
    Sheets("Phone Audit").Activate '***************
    Range("E49:T49").Select
    Selection.Copy
    Sheets("Data Phone").Activate
    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").Activate
    Range("H5:J5").Select
    Sheets("Data Phone").Visible = xlSheetVeryHidden
    ActiveWorkbook.Save
End Sub

Cheers

John

Upvotes: 0

Ralph
Ralph

Reputation: 9434

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

Upvotes: 1

Related Questions