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

 

[jasonb75]

What happens if you cut out all of the pointless selecting and paste directly from the source?

Try replacing

Range("A2:D8").Select
                        Selection.Copy
                        Sheets("Vital Stats Report").Select
                        Range("BL2").Select
                        ActiveSheet.Paste

With

Range("A2:D8").Copy Sheets("Vital Stats Report").Range("BL2")

 

[jeremiah_j2k]

That fixed that part.. some parts of this codes are from the macro that i've recorded that's why there are lines that are unnecessary. I ran it again and got the same error on "ActiveSheet.Paste" as shown below.. can you advise how to address this pls?

              ActiveSheet.Paste
              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
              Sheets("Vital Stats Report").Select
              Application.CutCopyMode = False

 

[jasonb75]

You would need to do similar with the Selection code above that line.

Looking at your recorded code, I'm, not going to attempt that part without an explanation as to what you're attempting to do with the section from Range("AU21:AX21").Select to the line that is currently causing the problem. It looks as if you're copying 2 cells and pasting to a larger range which makes no sense.

 

[jeremiah_j2k]

I'm trying to copy the value of Range("AU21:AX21").Select to the columns A:D on Sheets("Running Backlog").Select. The goal is to append the value just below the last entry on the table

 

[jasonb75]

Try replacing

              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

With

Dim lRow As Long
With Worksheets("Running Backlog")
    nrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    .Cells(nrow, 1).Resize(, 2).Value = Worksheets("Vital Stats Report").Range("AU21:AX21").Value
End With

You will likely find that fixing one thing will break another because of the mess that the macro recorder creates.

 

[jeremiah_j2k]

Yes it's now copying the value of Worksheets("Vital Stats Report").Range("AU21:AX21") to Worksheets("Running Backlog") but it's just adding row and not removing duplicates..

 

[jasonb75]

Sounds like you've deleted too much of the code. As far as I can see, the duplicates should have been deleted before that section in a part that hasn't (or at least shouldn't have) been changed.

 

[jeremiah_j2k]

I removed the code as suggested and only adjusted this part .Cells(nrow, 1).Resize(, 2) with .Cells(nrow, 1).Resize(, 4) for it to paste the value to columns A:D

 

[jasonb75]

Then I can see no reason for it to fail. As I said, fixing one thing may break another. I can't see the code on your screen from here so I can only base my opinions and suggestions on assumptions of what you might have done.