G
Guest
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
ActiveSheet.Paste
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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
(e-mail address removed)
command:-
Can you try selecting the sheet first, just in case it does not know the
'activesheet'
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
above error message and when debugged its highlighting the followingJonW said:When running a macro (that ran within XL 2000) within 2003. We get the
command:-
G
Guest
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
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
(e-mail address removed)
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
(e-mail address removed)
G
Guest
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
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
(e-mail address removed)
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.
Are you running this from a button?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
after it has tried to copy the selected cells. See full Macro below (==>JonW said:Nick,
The application.cutcopymode command appears near the bottom of the file
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.
G
Guest
Nick,
We are running via a button. As we have several buttons on the instructions sheet doing several different tasks.
Jon
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
(e-mail address removed)
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
(e-mail address removed)
instructions sheet doing several different tasks.JonW said:Nick,
We are running via a button. As we have several buttons on the
G
Guest
Nick,
As far as I'm aware this is just a standard Command Button with the macro attached!
Sorry to be a pain
Jon
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
(e-mail address removed)
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
(e-mail address removed)
G
Guest
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
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
N
Nick Hodge
Jon
Would you like to mail me the book? Take the obvious bits out of the email
address
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
Would you like to mail me the book? Take the obvious bits out of the email
address
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)