Since I'm dependent on calling ClearContents and ClearFormats I found a
pretty nice solution. All what I do is I remember the clipboard
manually. I store it when a sheet gets deactivated and read it back
after the new worksheet was activated. On save I clear the clipboard.

Works perfect!

Cheers,
Reto
www.collaboral.com

Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As
Long) As Long

Public Sub GetClipboard()
On Error Resume Next

m_clipboard.GetFromClipboard
m_clip = m_clipboard.GetText
End Sub

Public Sub SetClipboard()
On Error Resume Next

m_clipboard.SetText m_clip
m_clipboard.PutInClipboard
End Sub

Public Sub ClearClipboard()
On Error Resume Next

OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Clear the clipboard to maintain the standard behavior of the
Excel clipboard which gets cleared after a save
ClearClipboard
End Sub

Private Sub Workbook_SheetActivate(ByVal Target As Object)
Call Excel.ActiveSheet.UsedRange.ClearContents
Call Excel.ActiveSheet.UsedRange.ClearFormats

SetClipboard
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Target As Object)
GetClipboard
End Sub