VBA Copy & Paste Stopped Working

Hi guys,

Got a strange one for you.
Last Friday, my department at work used numerous macros to perform daily tasks.
On Tuesday when we came in, they had all stopped working.

The code is basically what I have posted below. It seems that when you open a new workbook, the clipboard is being emptied and therefore the paste is failing with the following error:

"Run-time error '1004': PasteSpecial method of Range class failed"

Has anyone seen this error before? Any M$ updates that may have caused it?
I am using Excel 2003.

   
 'copies column B to E in source worksheet
    Columns("B:E").Select
    Selection.Copy
    
    'Opens destination worksheet
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Bob\Desktop\Destination.xls"
          
    'selects the first sheet in destination worksheet
    Sheets(1).Select
    
    'attempts to paste into destination worksheet
    Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Cheers,
Adam

I hope the following codes will be helpful

    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Set Sht1 = Cells.Parent
    Set Sht2 = Workbooks.Open(Filename:= _
        "C:\Documents and Settings\Bob\Desktop\Test.xls").Sheets(1)
    Sht2.Columns("B:E").Value = Sht1.Columns("B:E").Value

Originally Posted by amulee

I hope the following codes will be helpful

    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Set Sht1 = Cells.Parent
    Set Sht2 = Workbooks.Open(Filename:= _
        "C:\Documents and Settings\Bob\Desktop\Test.xls").Sheets(1)
    Sht2.Columns("B:E").Value = Sht1.Columns("B:E").Value

Hi Amulee,

That code will work and thank you heaps but unfortunately, we have 30+ macros all written by 10+ different people with no instructions and I'm trying to understand what may have caused this issue to only start happening now in the hope i can fix that rather than try and upgrade every single macro.

If Microsoft has released a patch that may have caused this, i'd love to know so I can get the I.T dept to rollback the change.


Cheers,
Adam