|
New Macro error after Office Upgrade
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? |
New Macro error after Office Upgrade
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.) Marc wrote: 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 |
New Macro error after Office Upgrade
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" wrote: 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.) Marc wrote: 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 |
New Macro error after Office Upgrade
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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson |
New Macro error after Office Upgrade
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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson |
New Macro error after Office Upgrade
Do you have merged cells in those ranges?
What happens if you do it manually? Marc wrote: 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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson -- Dave Peterson |
New Macro error after Office Upgrade
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" wrote: Do you have merged cells in those ranges? What happens if you do it manually? Marc wrote: 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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson -- Dave Peterson |
New Macro error after Office Upgrade
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. "Marc" wrote: 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" wrote: Do you have merged cells in those ranges? What happens if you do it manually? Marc wrote: 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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson -- Dave Peterson |
New Macro error after Office Upgrade
I'm out of guesses.
Sorry. Marc wrote: 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" wrote: Do you have merged cells in those ranges? What happens if you do it manually? Marc wrote: 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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
New Macro error after Office Upgrade
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").RefersTo Range Top_DBase.ClearContents Set dBase = Workbooks("ControlOpstat01.xls").Names("dbase").Re fersToRange 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? Marc wrote: 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. "Marc" wrote: 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" wrote: Do you have merged cells in those ranges? What happens if you do it manually? Marc wrote: 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" wrote: 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. Marc wrote: 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" wrote: 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.) Marc wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 05:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com