Run-time error 1004 when formulabar, gridlines and headings are hidden

[jeremiah_j2k]

Hello All,

I's getting run-time error 1004 when running a simple macro to copy and paste a few cells between two sheets. I noticed that the code is working fine if formulabar, gridlines and headings are visible. I need to secure the formulas and run the code on my worksheet but im getting the error "Paste method of Worksheet class failed".. pls advise what's wrong with my code and how to correct them. i would appreciate any help

Here's my code to hide the formulabar, gridlines and headings

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False
    Application.DisplayFormulaBar = False
End Sub

I also tried this code to hide my formulas but also had the same error

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim formula As Range
On Error Resume Next
   Sh.Unprotect Password:="password"
   With Selection
   .Locked = False
   .FormulaHidden = False
End With
If Target.Cells.Count = 1 Then
If Target.HasFormula Then
   With Target
   .Locked = True
   .FormulaHidden = True
End With
   Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
   ElseIf Target.Cells.Count > 1 Then
   Set formula = Selection.SpecialCells(xlCellTypeFormulas)
   If Not formula Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With
   Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
End If
   On Error GoTo 0

End Sub

Here's the code that i use to copy and paste as well as to sort the column from the other sheet

Sub copyRunning()
Application.ScreenUpdating = False
If Range("AY21").Value >= 1 Then
    If WorksheetFunction.CountIf(Worksheets("Running Backlog").Columns(4), Worksheets("Vital Stats Report").Range("AX21")) Then
        If MsgBox("The Running Backlog for " & Range("AX21") & " already exist. Do you want to overwrite it instead?", vbQuestion + vbYesNo, "APAC CC Running Backlog") = vbYes Then
     'this will delete the duplicate entries on the table (",)
              With Sheets("Running Backlog")
              LR = .Range("D" & Rows.Count).End(xlUp).Row
              For i = LR To 1 Step -1
                  If IsNumeric(Application.Match(.Range("D" & i).Value, Sheets("Vital Stats Report").Range("AX21"), 0)) Then .Rows(i).Delete
              Next i
            'this will update the table with data from Vital Stats Report
              Range("AU21:AX21").Select
              Selection.Copy
              Sheets("Running Backlog").Select
              Range("E1").Select
              Selection.End(xlDown).Select
              Selection.End(xlToLeft).Select
              Selection.End(xlUp).Select
              ActiveCell.Offset(1, 0).Range("A1").Select
              ActiveSheet.Paste
              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
              Sheets("Vital Stats Report").Select
              Application.CutCopyMode = False
              'Selection.ClearContents
              MsgBox ("Running Backlog for " & Range("AX21") & " has been added to the table")
                        Application.ScreenUpdating = False
                        Sheets("Running Backlog").Select
                        Columns("D:D").Select
                        ActiveWorkbook.Worksheets("Running Backlog").Sort.SortFields.Clear
                        ActiveWorkbook.Worksheets("Running Backlog").Sort.SortFields.Add2 Key:=Range( _
                            "D1:D9"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
                            xlSortNormal
                        With ActiveWorkbook.Worksheets("Running Backlog").Sort
                            .SetRange Range("A1:D9")
                            .Header = xlGuess
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
                        Range("A2:D8").Select
                        Selection.Copy
                        Sheets("Vital Stats Report").Select
                        Range("BL2").Select
                        ActiveSheet.Paste
                        Range("C19").Select
                        Application.CutCopyMode = False
                        ActiveWorkbook.RefreshAll
              End With
               
                Else
                    Exit Sub
        End If
    End If
Else
              Application.ScreenUpdating = False
              Range("AU21:AX21").Select
              Selection.Copy
              Sheets("Running Backlog").Select
              Range("E1").Select
              Selection.End(xlDown).Select
              Selection.End(xlToLeft).Select
              Selection.End(xlUp).Select
              ActiveCell.Offset(1, 0).Range("A1").Select
              ActiveSheet.Paste
              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
              Sheets("Vital Stats Report").Select
              Application.CutCopyMode = False
              'Selection.ClearContents
              MsgBox ("Running Backlog for " & Range("AX21") & " has been added to the table")

                        Sheets("Running Backlog").Select
                        Columns("D:D").Select
                        ActiveWorkbook.Worksheets("Running Backlog").Sort.SortFields.Clear
                        ActiveWorkbook.Worksheets("Running Backlog").Sort.SortFields.Add2 Key:=Range( _
                            "D1:D9"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
                            xlSortNormal
                        With ActiveWorkbook.Worksheets("Running Backlog").Sort
                            .SetRange Range("A1:D9")
                            .Header = xlGuess
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
                        Range("A2:D8").Select
                        Selection.Copy
                        Sheets("Vital Stats Report").Select
                        Range("BL2").Select
                        ActiveSheet.Paste
                        Range("C19").Select
                        Application.CutCopyMode = False
                        ActiveWorkbook.RefreshAll
       
Application.ScreenUpdating = False
End If
End Sub

 

[jeremiah_j2k]

it's working as needed now after I closed and re-open the workbook thank you for your help.. i appreciate it