Macro stops working after saving workbook

[jmpatrick]

Hello, all.

I have this fabulous macro that (when a link is clicked) combines data and copies the results suitable for pasting outside of Excel. This works great UNTIL I hit save. Once I do it no longer produces pasteable text unless I close and reopen the Workbook. It works fine as long as I don't save the file.

Sub ShippingInstructions()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Sheets("ShippingInstructions").Visible = True
    
    Sheets("ShippingInstructions").Range("A1") = Cells(ActiveCell.Row, "B")
    Sheets("ShippingInstructions").Range("E1") = Cells(ActiveCell.Row, "E")
    Sheets("ShippingInstructions").Select
    Range("G1").Select

    Dim objData As New DataObject
    Dim strTemp As String
    strTemp = Replace(ActiveCell.Value, Chr(10), vbCrLf)
    objData.SetText (strTemp)
    objData.PutInClipboard
 
    Sheets("Calendar").Select
 
    Sheets("ShippingInstructions").Visible = False
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

 

[jmpatrick]

Update: I deleted my Excel Cache Files an hour ago and it has been working correctly ever since. Coincidence? We shall see.

FILE > OPTIONS > SAVE > DELETE CACHED FILES

If this is the solution I'm wondering if the cached files can be deleted with VBA.

 

[Zyndstoff]

Update: I deleted my Excel Cache Files an hour ago and it has been working correctly ever since. Coincidence? We shall see.

FILE > OPTIONS > SAVE > DELETE CACHED FILES

If this is the solution I'm wondering if the cached files can be deleted with VBA.

That most likely did it. I doubt that there are specific commands for doing that in VBA, in a crude way you might be able to do it via the sendkeys-command, maybe?

 

[jmpatrick]

That most likely did it. I doubt that there are specific commands for doing that in VBA, in a crude way you might be able to do it via the sendkeys-command, maybe?

This is still causing me grief, but I just learned something interesting about it. When it stops working correctly I can close Excel and I'm then able to paste the last macro-generated contents still in the clipboard.

Very strange.

 

[NoSparks]

Very strange

Not really.
Hit the Windows Logo + v keys to bring up the Windows Clipboard and I suspect you'll see all the strTemp strings your code has been putting into it.

 

[jmpatrick]

Still frustrated, I went through this whole macro to get to the bottom of this issue.

This is the bad juju:

    Dim objData As New DataObject
    Dim strTemp As String
    strTemp = Replace(ActiveCell.Value, Chr(10), vbCrLf)
    objData.SetText (strTemp)
    objData.PutInClipboard

I originally added this because when I pasted out of Excel I would get double quotes around all my text. The code above stopped that but apparently didn't play nice with the clipboard. So I hunted around for an alternative and stumbled on a person that eliminated the need for a carriage return by simply splitting the two lines of text into two separate cells. I then created a named range with just the two cells. This pastes perfectly without quotes. Thanks for everyone's help.