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]

Can you elaborate on how you're verifying things don't work?
I ask because setting up a test workbook from your descriptions and picture
the code from post 1 allows me to paste from the clipboard into NotePad at any time before save or after save or while closed or after re-opening.

OK, that's crazy. It's working fine for me today as well. We'll see how things look Monday morning. Thanks for your help.

 

[jmpatrick]

I'm sorry to report that this is still broken. I tried the suggestion above and it didn't work, meaning I can't paste the contents of G1 before or after a save.

Here's what I've learned:

1. The macro works fine as long as the user DOES NOT click Save.

2. After the user clicks Save the first part of the macro still works, meaning I can see the text going to the Clipboard by monitoring Clipboard in Excel, but the text can't be pasted anywhere (inside or outside of Excel).

 

[jmpatrick]

I THINK I figured this out!

Going to do some more testing and report back.

 

[jmpatrick]

Sadly, I must again report that this is NOT fixed. Can't figure out why I cannot paste Excel data outside of Excel AFTER I click save.

 

[NoSparks]

I still don't know how you are verifying this.
If you open, let's say NotePad, and bring up the viewer for the clipboard your code writes to, the Windows clipboard, are you saying that double clicking the last (or any) thing written to that clipboard from within Excel doesn't get pasted to NotePad ?

 

[jmpatrick]

I still don't know how you are verifying this.
If you open, let's say NotePad, and bring up the viewer for the clipboard your code writes to, the Windows clipboard, are you saying that double clicking the last (or any) thing written to that clipboard from within Excel doesn't get pasted to NotePad ?

Hope this helps...

 

[NoSparks]

And what if you do that from the clipboard your code is writing to rather than the Excel clipboard ?

Windows Start > Settings > Clipboard >

 

[jmpatrick]

And what if you do that from the clipboard your code is writing to rather than the Excel clipboard ?

Windows Start > Settings > Clipboard >

View attachment 55177

Yeah, that's how I had it set on my PC...

 

[NoSparks]

Sorry, all I can do is wish you luck and move on as I can not replicate the issue you describe.

 

[jmpatrick]

Sorry, all I can do is wish you luck and move on as I can not replicate the issue you describe.

Thanks for trying. There's so many variables at play here. Maybe a program other than Excel is breaking it. Who knows.