Copy and Paste not working during BeforeSave

**carsto** · 06-23-2015, 09:39 AM

The Live worksheet contains formulas linked to other workbooks so in an effort to retain a static copy of the calculations at the time of saving I'm copying and pasting the Live sheet values to the Static sheet.

If I run this manually it runs fine.
If I call it from the BeforeSave or embed it into BeforeSave it steps through the lines of code but does nothing.

When BeforeSave initiates the activesheet is always Live.

Sub SaveStatic()

‘just in case their currently false due to other code
Application.EnableEvents = True
Application.ScreenUpdating = True

'clear the Static Sheet
    Static.Cells.Clear

'Copy & Paste Live VALUES  to Static
    Live.Range("A1:R79").Copy
    Static.Activate
    Static.Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Live.Activate

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

**quekbc** · 06-23-2015, 08:04 PM

I noticed that you used codenames for the worksheet objects (Static and Live). As far as I'm aware, you shouldn't be able to use the code name "Static" as a codename for a worksheet.

I ran your code, with a small adjustment for the use "Static" to refer to the worksheet name instead and it seems to be working on my end. Not too sure what's going on on your version. Are you able to send the original Excel file (with any important data removed of course) for us to play with?

The code I used for my testing as below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Call SaveStaticEnd Sub

Sub SaveStatic()

'just in case their currently false due to other code
Application.EnableEvents = True
Application.ScreenUpdating = True

'clear the Static Sheet
    Worksheets("Static").Cells.Clear

'Copy & Paste Live VALUES  to Static
    Live.Range("A1:R79").Copy
    Worksheets("Static").Activate
    Worksheets("Static").Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Live.Activate

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub