Error in macro when auto-pasting code

[Reapz]

Hey all,

I'm new to VBA but have had it working well for me until the other night when a user complained that when he ran a macro, it came up with the error "Paste method of Worksheet class failed". I should note that this function has worked time and time again, so I'm lost as to why it just started. I also found tonnes of answers with a Google search but they were all relative to that persons code.

So this is the code I am using to paste from the clipboard into another tabbed worksheet (apologies if the code is horrible) - Also the part where the debugger stops (and highlights) is ActiveSheet.Paste

Sub PasteDemand()
'
' PasteDemand Macro
'

'
    Sheets("Demand").Visible = True
    Sheets("Demand").Select
    Application.Volatile (False)
    ActiveSheet.Range("A2:X400").Clear
    Cells.Select
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.DisplayAlerts = True
    Columns("D:D").Select
    With Selection
    Selection.NumberFormat = "0"
    .Value = .Value
End With
     
    Sheets("Demand").Visible = False
    Sheets("Cover").Select
    Range("C5").Select
End Sub

Thank you for any help!

[Bernie Deitrick]

Did you add this line recently?

ActiveSheet.Range("A2:X400").Clear

Any action other than selecting will clear the clipboard.

[Reapz]

Did you add this line recently?

ActiveSheet.Range("A2:X400").Clear

Any action other than selecting will clear the clipboard.

Yes I added that around two weeks ago and it seemed to be working perfectly prior to just the other day. I need the data that was previously in that range to be cleared before the data in a separate workbook is pasted.. Is that possible?

Thanks for your help.

[Bernie Deitrick]

Sure - the best way is to clear it first before copying, but to not change your workflow, we can check after the paste for the cells that were not pasted into....

Sub PasteDemand2()
'
' PasteDemand Macro
'
    Dim c As Range

    Sheets("Demand").Visible = True
    Sheets("Demand").Select
    Application.Volatile (False)
    Cells.Select
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    For Each c In ActiveSheet.Range("A2:X400")
        If Intersect(c, Selection) Is Nothing Then c.Clear
    Next c
    Application.DisplayAlerts = True
    Columns("D:D").Select
    With Selection
        Selection.NumberFormat = "0"
        .Value = .Value
    End With
     
    Sheets("Demand").Visible = False
    Sheets("Cover").Select
    Range("C5").Select
End Sub

[Reapz]

Sure - the best way is to clear it first before copying, but to not change your workflow, we can check after the paste for the cells that were not pasted into....

Sub PasteDemand2()
'
' PasteDemand Macro
'
    Dim c As Range

    Sheets("Demand").Visible = True
    Sheets("Demand").Select
    Application.Volatile (False)
    Cells.Select
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    For Each c In ActiveSheet.Range("A2:X400")
        If Intersect(c, Selection) Is Nothing Then c.Clear
    Next c
    Application.DisplayAlerts = True
    Columns("D:D").Select
    With Selection
        Selection.NumberFormat = "0"
        .Value = .Value
    End With
     
    Sheets("Demand").Visible = False
    Sheets("Cover").Select
    Range("C5").Select
End Sub

Thank you, that worked perfectly, enjoy some +rep! So can anyone explain to me why this would have worked for the past few months, but then all of a sudden it became a problem? Or at least can anyone confirm my understanding of this - that the code I had also cleared the clipboard, thus when it went to paste there was nothing to actually paste.. right?

Which brings me back to being confused because it worked for so long..

Anyway thanks again!

I think someone here might have a similar problem.

Oops I must have posted the thread twice by accident? My bad, sorry!

[Bernie Deitrick]

The only reason that I can think of that could have led you to think the macro worked is that you have multiple versions of the macro, some changed and some not, in different versions of the workbook. The .Clear command will always clear the clipboard.

[Norie]

I think someone here might have a similar problem.