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]

I've noticed that everything up to this part works after a save:

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

That's the part that isn't working.

 

[Zyndstoff]

What does "does not work" mean? Any error prompts?

 

[Joe4]

What does "does not work" mean? Any error prompts?

They probably won't see them because they disabled alerts.

I would comment out the line disabling alerts and try again, and see if you get any error messages.
Also, try stepping through the code, one line at a time, using the F8 key, and see the path that your code takes and if it is exiting early.

 

[jmpatrick]

What does "does not work" mean? Any error prompts?

No error prompts. I disabled alerts before checking.

Does not work in this case means the macro runs but when I paste the resulting text it's blank. It doesn't copy G1 as it should. This is only if I hot save when the file is open. If I don't hit save it works fine.

 

[jmpatrick]

This part was added because if I just copy the data in G1 and paste it outside of Excel I get double quote marks and I lose the line break.

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

 

[jmpatrick]

G1 has this formula in it:

=D1&" "&E1&"  "&F1&CHAR(10)&"Deliver "&B1&" "&C1

 

[Zyndstoff]

Hm... Try this:

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(Sheets("ShippingInstructions").Range("G1").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]

Hm... Try this:

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(Sheets("ShippingInstructions").Range("G1").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

No luck. Doesn't work at all, before or after a save.

 

[NoSparks]

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.