New Macro error after Office Upgrade

[Guest]

We recently upgraded some PCs from Office 2000 to Office 2003, and now when
we run an Excel Macro that copies data, I am getting the following error.

Run time Error 1004 "Paste method of Worksheet class failed"

The code causing the exception error is "ActiveSheet.Paste". Is this command
no longer legal in Office 2003?? Is there some other VB command that should
be used other than "ActiveSheet.Paste" for 2003 or a security patch that is
needed?

 

[Dave Peterson]

That one line isn't the culprit.

But there could be a few reasons...

1. Your worksheet is protected

2. Whatever you copied isn't in the clipboard at that moment.
if you add
msgbox application.cutcopymode
right before the .paste line, what do you see?

3. You're pasting into a range that can't be the same size:
Range("a:a").Copy
Range("b2").Select
ActiveSheet.Paste

(I'm guessing #2.)

 

[Guest]

Dave,

I added the code before the paste and msgbox opens with a 1 showing?

The macro works fine on a Office 2000 system, try to launch on Office 2003
you get the error, so I not sure about #1 or #3 are an issue, unless some
rules have changed across versions? Let me know what you think about the
above result.

 

[Dave Peterson]

That means that there was something in the clipboard.

If you had something copied, you'll see 1 (xlCopy). If you Cut something, then
you would see 2 (xlCut).

Is it the worksheet protection?

Is it the cell you're copying?

You may want to share a few more lines of code.

 

[Guest]

Dave,
Worksheet is not protected. I'm copying a range, see below code example.

'Modify for File Name and Org
strFileName = "Opst_200.xls"
strOrgName = "200"

' Path to Hotel database file
Windows(strFileName).Activate
Windows.Application.ScreenUpdating = False
Application.Goto Reference:="top_dbase"
Selection.CurrentRegion.Select
Selection.ClearContents
Windows("ControlOpstat01.xls").Activate
Application.Goto Reference:="dbase"
Selection.AutoFilter Field:=1, Criteria1:=strOrgName
Selection.Copy
Windows(strFileName).Activate
Application.Goto Reference:="top_dbase"
'MsgBox Application.CutCopyMode
ActiveSheet.Paste
Selection.Name = "dbase"

 

[Dave Peterson]

Do you have merged cells in those ranges?

What happens if you do it manually?

Dave,
Worksheet is not protected. I'm copying a range, see below code example.

'Modify for File Name and Org
strFileName = "Opst_200.xls"
strOrgName = "200"

' Path to Hotel database file
Windows(strFileName).Activate
Windows.Application.ScreenUpdating = False
Application.Goto Reference:="top_dbase"
Selection.CurrentRegion.Select
Selection.ClearContents
Windows("ControlOpstat01.xls").Activate
Application.Goto Reference:="dbase"
Selection.AutoFilter Field:=1, Criteria1:=strOrgName
Selection.Copy
Windows(strFileName).Activate
Application.Goto Reference:="top_dbase"
'MsgBox Application.CutCopyMode
ActiveSheet.Paste
Selection.Name = "dbase"

 

[Guest]

Dave,

No merged cells, I tried manually and worked with no errors. I tried
replacing the StrFileName with the actual file name and still error at same
point.

 

[Guest]

Dave,
I did miss one issue when running this manually, I'm getting the following
warning on screen when opening file: Cannot find 'C:\Program Files\Microsoft
Office\Office 11\Library\MSquery\XLQuery.XLA'!Register.Dclick, which has been
assaigned to run each time[Opst_200.xls]A is opened. Continuing could cause
errors. Cancel open Yes/No button on form. I answered no when I ran my test.
I confirmed when the macro runs that the Opst_200 file has opened also via
that method.

Dave,

No merged cells, I tried manually and worked with no errors. I tried
replacing the StrFileName with the actual file name and still error at same
point.

 

[Dave Peterson]

I'm out of guesses.

Sorry.

Dave,

No merged cells, I tried manually and worked with no errors. I tried
replacing the StrFileName with the actual file name and still error at same
point.

 

[Dave Peterson]

It sounds like there's something happening on the worksheet_activate event
(maybe???).

This doesn't answer the question, but I think that this is equivalent to your
code:

Option Explicit
Sub testme01()

Dim Top_DBase As Range
Dim dBase As Range
Dim strFileName As String
Dim strOrgName As String

strFileName = "Opst_200.xls"
strOrgName = "200"

Set Top_DBase = Workbooks(strFileName).Names("Top_dbase").RefersToRange
Top_DBase.ClearContents

Set dBase = Workbooks("ControlOpstat01.xls").Names("dbase").RefersToRange

dBase.AutoFilter field:=1, Criteria1:=strOrgName

dBase.Copy _
Destination:=Top_DBase.Cells(1)

Top_DBase.Resize(dBase.Rows.Count, dBase.Columns.Count).Name = "dbase"

End Sub

Did you really meant to use "dbase" in that last portion?

Dave,
I did miss one issue when running this manually, I'm getting the following
warning on screen when opening file: Cannot find 'C:\Program Files\Microsoft
Office\Office 11\Library\MSquery\XLQuery.XLA'!Register.Dclick, which has been
assaigned to run each time[Opst_200.xls]A is opened. Continuing could cause
errors. Cancel open Yes/No button on form. I answered no when I ran my test.
I confirmed when the macro runs that the Opst_200 file has opened also via
that method.

Dave,

No merged cells, I tried manually and worked with no errors. I tried
replacing the StrFileName with the actual file name and still error at same
point.