Encountering a Debug error code on first use of a macro but then it goes away?

[Lyryx]

"Run-time error '1004':

PasteSpecial method of Range class failed"


This occurs on the first use of a macro I use in a work book that maintains a pretty large database whenever I make changes to the macro. It only occurs once and then it is no longer an issue. I can close and reopen the workbook and not encounter this error again until I make a change to the workbook. It's very strange.


The macro takes data entered into one sheet vertically in the same column and transposes it before pasting it into another sheets row on a table.
To prevent people from breaking the workbook I have the sheets protected (but the macro removes protection to do what it needs to do before re-enabling it)
Additionally I have used some code on the entire workbook to disable cut and click/drag functions outside of the macro use.

This line of code is being highlighted for the debug error from the macro below.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _​

:=False, Transpose:=True​

Sub PopulateProtect()
'
' PopulateProtect Macro
'


'
    ActiveSheet.Unprotect
    Sheets("Table").Select
    ActiveSheet.Unprotect
    Sheets("Input Worksheet").Select
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("B3:B12").Select
    Selection.Copy
    Sheets("Table").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Input Worksheet").Select
    Range("B3:B10").Select
    Selection.ClearContents
    Range("B3").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

This is the code I am using to disable copy/cut/drag in the workbook when the macro is not being operated.

Private Sub Workbook_Activate()
'' When making this the active workbook - run this code




' Defines variable
Dim oCtrl As Office.CommandBarControl
    'Disable all Cut menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
            oCtrl.Enabled = False
     Next oCtrl
        'Disable all Copy menus
         For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
                oCtrl.Enabled = False
         Next oCtrl
            ' Prevent Drag and Drop
            Application.CellDragAndDrop = False
End Sub


Private Sub Workbook_Deactivate()
'' When making another workbook the active workbook - run this code




' Define variable
Dim oCtrl As Office.CommandBarControl
'Enable all Cut menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
            oCtrl.Enabled = True
     Next oCtrl
        'Enable all Copy menus
         For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
                oCtrl.Enabled = True
         Next oCtrl
            ' Re-enable Drag and Drop
            Application.CellDragAndDrop = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'' When selecting cells in this workbook - run this code




    ' Prevent Drag and Drop, clear any copied data from the clipboard
    With Application
        .CellDragAndDrop = False
        .CutCopyMode = False 'Clear clipboard
    End With
End Sub

Hopefully this is enough information to help me figure this one out... it's really got me confused lol.

Thank you in advance.

 

[BiocideJ]

Definitely an odd occurrence.
The only thing I can think to try since it seems more like a VBA issue than an Excel issue is to go to the VBA Project screen (where the code is)
and from the top menu choose Debug>Compile VBA Project

If that gives you any Compile errors/problems, you will need to figure out what they are and attempt to correct them.
Outside of that, I am rather stumped.

 

[Lyryx]

Well I've been able to isolate the issue but have not been able to solve, the work around I've been using is to have a dummy piece of data that I can enter to sort of "Anchor" the data and then after the first Debug code has popped up the real data can be entered with no issues.


I ran the Compile VBA Project but had nothing returned after processing.

The Error seems to show up whenever I make a change to a macro that uses the Paste Special option. When I have a macro on the entire workbook that is disabling the cut/copy/paste functions outside of macro use.

For whatever reason this combination of code causes an error. But only once. Very strange, sorry I couldn't fix and Thank you BiocideJ for your help so far.