I wrote a macro to filter my worksheet and hide certain columns, then sort
and select the results and copy to the clipboard so I can paste into Word.
It worked fine - until I stuck in some more code to un-filter, un-sort, and
un-hide everything to reset the workbook. Then, when the code opens my Word
doc, the clipboard is empty. What am I doing wrong?

Ed

Here's the relevant sections. This works if I comment out between the ***.

' Put range on clipboard
ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Copy

***

' Reset worksheet
' Unhide everything
Sheets("Sheet1").Activate

Sheets("Sheet1").AutoFilterMode = False

With Cells
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With

' Resort to TIR No.
' Module 12
SortTIRNo

***

' Open template, which has Auto_Open macro
' to paste clipboard and make EFF tables
doc = "C:\Documents and
Settings\username\Desktop\Reports\TablesTemplate.d oc"
Set WD = CreateObject("Word.Application")
WD.Documents.Open doc
WD.Visible = True

End Sub

If the macro does almost anything to the sheet after the copy, Excel will
exist copy mode. You should do the copy as late as possible before the
paste.

--
Jim Rech
Excel MVP

Ouch - I was assuming the code would mimic operator activity, becuse I can
copy, then do other stuff, then pull up Word and manually paste. But it
doesn't surprise me, because I've run into that on other things. Thanks for
the info, Jim.

I was concerned that, because the Selection.Copy action leaves the cells
selected when I paste and do other things, the possibilities of an accident
erasing my data are too great for comfort. Also, before I copy, I have to
do a sort out of the normal data order. So I wanted to reset the sheet
before it got mangled or closed and saved incorrectly. What I did in the
meantime - and it looks like it may be permanent - was to set a message box
right after calling Word; I'm left with Word open so I can do stuff there,
but Excel is waiting in the background with a MsgBox that must be closed
before the data can be touched. I then moved the reset code after the
MsgBox, which will be ok'd after I finish with Word. Does that sound like
the best compromise? Or am I leaving myself open for something?

Ed

"Jim Rech" wrote in message
...
If the macro does almost anything to the sheet after the copy, Excel will
exist copy mode. You should do the copy as late as possible before the
paste.

--
Jim Rech
Excel MVP

I think leaving the Msgbox up is a good idea. Some developers in my shop
have used that technique without any problems.

If, by any chance, you are only interested in pasting the Excel data into
Word (no formats) you can do a non-Excel copy by using Windows API calls.
With these you do not have to worry about Excel clearing the clipboard on
you. It's basically the same as highlighting what's in the formula bar and
doing a Ctrl-c, only it can work on a range. Fwiw, the code is below. It
works on the current selection so you'd have to modify it to work on a range
object.

--
Jim Rech
Excel MVP

Public Declare Function GlobalAlloc32 Lib "Kernel32" Alias "GlobalAlloc" _
(ByVal wFlags As Long, ByVal dwBytes As Long) As Long

Public Declare Function GlobalLock32 Lib "Kernel32" Alias "GlobalLock" _
(ByVal hMem As Long) As Long

Public Declare Function OpenClipboard32 Lib "user32" Alias "OpenClipboard" _
(ByVal hwnd As Long) As Long

Public Declare Function GlobalUnlock32 Lib "Kernel32" Alias "GlobalUnlock" _
(ByVal hMem As Long) As Long

Public Declare Function lstrcpy32 Lib "Kernel32" Alias "lstrcpy" _
(ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

Public Declare Function CloseClipboard32 Lib "user32" Alias "CloseClipboard"
() As Long

Public Declare Function SetClipBoardData32 Lib "user32" _
Alias "SetClipboardData" (ByVal wFormat As Long, ByVal hMem As Long) As
Long

Declare Function GetClipboardData32 Lib "user32" Alias _
"GetClipboardData" (ByVal wFormat As Long) As Long

Declare Function EmptyClipboard32 Lib "user32" Alias "EmptyClipboard" () As
Long

Global Const CF_TEXT = 1

''Call this
Sub CB_SendData()
Dim StrBuf As String
Dim CurrRow As Range, CurrCell As Range
'Build a long string of cell values
' Tabs separate columns
' Carriage returns separate rows
For Each CurrRow In Selection.Rows
For Each CurrCell In CurrRow.Cells
StrBuf = StrBuf & CurrCell.Value & Chr(9)
Next
'Remove last Tab on row and add carriage return
StrBuf = Left(StrBuf, Len(StrBuf) - 1) & Chr(13)
Next
ClipBoard_SetData StrBuf
End Sub


Sub ClipBoard_SetData(MyString As String)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long
' Allocate moveable global memory.
hGlobalMemory = GlobalAlloc32(&H42, Len(MyString) + 1)
' Lock the block to get a far pointer to this memory.
lpGlobalMemory = GlobalLock32(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy32(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock32(hGlobalMemory) < 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere
End If
' Open the Clipboard to copy data to.
If OpenClipboard32(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Sub
End If
EmptyClipboard32 'Don't know if I really need this
' Copy the data to the Clipboard.
hClipMemory = SetClipBoardData32(CF_TEXT, hGlobalMemory)
OutOfHe
If CloseClipboard32() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Sub