Xl 2000 to 2003 - Paste Method of Worksheet class Failed.

J

JonW

When running a macro (that ran within XL 2000) within 2003. We get the above error message and when debugged its highlighting the following command:-
ActiveSheet.Paste
 
N

Nick Hodge

Jon

Can you try selecting the sheet first, just in case it does not know the
'activesheet'

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]


JonW said:
When running a macro (that ran within XL 2000) within 2003. We get the
Click to expand...
above error message and when debugged its highlighting the following
command:-
 
J

JonW

Nick,

Sheet is selected. see further details of macro

Sheets("Sales 2004").Select
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=-46
Range("O44").Select
ActiveSheet.Paste

regards

Jon
 
N

Nick Hodge

Jon

I suspect XL has lost what it wanted to paste. You don't show us the code
for copying, but if you do much after you have copied the chances are XL
will lose it's clipboard. (It doesn't work like most other apps). For
example, I expanded your code to copy a cell from Sheet1 A1 and it works ok.
Take the comment mark (') out of the application.cutcopy..... line and you
will get the error as I have replicated XL losing it's clipboard contents

Sub test()
Sheets("Sheet1").Range("A1").Copy
'Application.CutCopyMode = False
Sheets("Sales 2004").Select
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=-46
Range("O44").Select
ActiveSheet.Paste
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
J

JonW

Nick,

The application.cutcopymode command appears near the bottom of the file after it has tried to copy the selected cells. See full Macro below (==> indicates mark when entering debug). I've tried recording a similar macro to do the same thing. But when I've looked at the macro the ActiveSheet.Paste command isn't in the script! When added I still get the same message as above.

Jon

Sub LoadFood()
'
' LoadFood Macro
' Macro recorded 19/05/2004 by forstdw
'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("All Accounts").Select
Range("A1").Select
With Selection.QueryTable
.Connection = "TEXT;K:\IT\AdhocSalesReports\SR_A0001c.csv"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2)
.Refresh BackgroundQuery:=False
End With
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Instructions").Select
'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("All Acct Sales").Select
ActiveWindow.ScrollRow = 1
Range("A2").Select
With Selection.QueryTable
.Connection = "TEXT;K:\IT\AdhocSalesReports\SR_A0001b.csv"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 9, 2, 2)
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:C854").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Other Foodservice"
Range("A15").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sales 2004").Select
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=-46
Range("O44").Select
==> ActiveSheet.Paste
Range("W44").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=12
Range("AE44").Select
ActiveSheet.Paste
Range("AM44").Select
ActiveSheet.Paste
Range("AU44").Select
ActiveSheet.Paste
Range("BC44").Select
ActiveSheet.Paste
Range("BK44").Select
ActiveSheet.Paste
Range("BS44").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=18
Range("CB44").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=47
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.LargeScroll Down:=1
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Other Food Services Structure").Select
ActiveWindow.LargeScroll Down:=-1
Range("B9").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=38
Range("E72").Select
ActiveWindow.LargeScroll Down:=-1
Range("E9").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Instructions").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("All Acct Sales").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Instructions").Select
Range("B10").Select
End Sub
 
N

Nick Hodge

Jon

Are you running this from a button?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]


JonW said:
Nick,

The application.cutcopymode command appears near the bottom of the file
Click to expand...
after it has tried to copy the selected cells. See full Macro below (==>
indicates mark when entering debug). I've tried recording a similar macro
to do the same thing. But when I've looked at the macro the
ActiveSheet.Paste command isn't in the script! When added I still get the
same message as above.
 
J

JonW

Nick,

We are running via a button. As we have several buttons on the instructions sheet doing several different tasks.

Jon
 
N

Nick Hodge

Jon

If this is an activeX control, from the control toolbox try setting it's
TakeFocusOnClick property to False

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]


JonW said:
Nick,

We are running via a button. As we have several buttons on the
Click to expand...
instructions sheet doing several different tasks.
 
J

JonW

Nick,

As far as I'm aware this is just a standard Command Button with the macro attached!

Sorry to be a pain

Jon
 
N

Nick Hodge

John

Can you right click on it, select properties? If it shows a sheet of
properties, change the TakeFocusOnClick one to false. If it doesn't show a
sheet of properties then you have the old 'forms' version. Let us know...we
will fix this ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
J

JonW

Nick,

When I right click I don't get a Properties option!
I have a feeling this could be a drawing box rather than a command control box!!

Jon
 
Top