CutCopyMode=False does not work

[Horatio J. Bilge, Jr.]

I am using vba in one workbook to make changes in a target workbook. I used
application.cutcopymode=false to clear the clipboard, but it doesn't seem to
work correctly. I have the clipboard visible to the side of the window, and
all of the copy/paste operations remain in the clipboard.

Here is the code I am using:
Option Explicit
Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False

FileName = Application.GetOpenFilename
If FileName = False Then Exit Sub

Dim WB As Workbook
Set WB = Workbooks.Open(FileName)

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
WB.Worksheets("Sheet1").Range("A1").PasteSpecial
WB.Worksheets("Sheet2").Range("A33").Copy
WB.Worksheets("Sheet2").Range("A44").PasteSpecial xlPasteFormats
WB.Worksheets("Sheet2").Range("B5:G5").Copy
WB.Worksheets("Sheet2").Range("B7:G9").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
WB.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub

 

[Héctor Miguel]

hi, Horatio !

I am using vba in one workbook to make changes in a target workbook.
I used application.cutcopymode=false to clear the clipboard, but it doesn't seem to work correctly.
I have the clipboard visible to the side of the window, and all of the copy/paste operations remain in the clipboard...

windows clipboard ?... see:
-> http://www.cpearson.com/Excel/clipboard.htm

office clipboard ?... see:
-> http://www.mvps.org/skp/off00004.htm#2
-> http://support.microsoft.com/default.aspx?scid=kb;en-us;q207438
-> http://techsupt.winbatch.com/TS/T000001071F4.html
-> http://support.microsoft.com/default.aspx?scid=kb;ES;A196620

hth,
hector.

 

[Horatio J. Bilge, Jr.]

Your first link about the office clipboard looked good, but it didn't work
for me:
Application.CommandBars("Clipboard").Controls(4).Execute

I found a comment on another forum that said you can't clear the office
clipboard with vba. I have tried recording a macro for clearing the
clipboard, but it just comes up blank. Instead I have settled for just
closing the clipboard:
Application.CommandBars("Task Pane").Visible=False

But since the Task Pane is only in newer versions of Excel, is that line of
code going to be an issue for users with an older version of Excel?

Thanks,
~ Horatio

 

[Héctor Miguel]

hi, Horatio !

Your first link about the office clipboard looked good, but it didn't work for me:
Application.CommandBars("Clipboard").Controls(4).Execute

office clipboard for xl2000, try with:
-> Application.CommandBars.FindControl(ID:=3634).Execute

I found a comment on another forum that said you can't clear the office clipboard with vba.
I have tried recording a macro for clearing the clipboard, but it just comes up blank.
Instead I have settled for just closing the clipboard:
Application.CommandBars("Task Pane").Visible=False
is that line of code going to be an issue for users with an older version of Excel?

office clipboard "moved" to task-pane (xl2002 / xl2003) and AFAIK...
- it does not expose its OM to VBA :-((
- closest approach ?... see Michael Pierrone (post # 6) in: -> http://tinyurl.com/2j8y4x

hth,
hector.

 

[Horatio J. Bilge, Jr.]

It looks like the code will always be dependent on the version of excel being
used.
I found a different solution, though. There are samples for bypassing the
clipboard, instead of using Copy/Paste, at
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
I have tried it out, and it works great.

Thanks for the help.
~ Horatio