[SOLVED] I make a copy of an Excel sheet that has Automatic calculate turned off, then copy/paste values but auto calc turns on before the paste values

I have a spreadsheet that has its automatic calculate turned to manual once it has been Emailed. I do not use protect sheet as formulas will still get updated if the data source gets updated following the mail.

On a rare occasion it may be necessary to mail the sheet again with changes fed from other sheets. At that time I have code that makes a copy of the sheet and then copies and pastes as values leaving the original sheet feeding in tack to become the version II.

I am trying to avoid doing the copy/paste values at the time of mail as I would like to avoid having two copies of the sheet in the workbook if its not necessary.

The problem is that even though automatic updating is off when the sheet is copied it appears that it gets turned off before I am then able to copy and past values.

Does anyone have an idea how I can stop data feeding into the formulas on the sheet before I can paste them as values?

UPDATES to add code.

Code to turn off auto calculations

Sub Turn_AutoUpdate_OFF()

' ***** STOPS alutomatic formular updating

' x - Defined Cell Names  Lock_LABEL
' x - Image               Lock_ON    Lock_OFF

Application.ScreenUpdating = False  ' do not see screen updating

If ActiveSheet.Name = "4_Transport" Then

    ' Make ON lock Small
    ActiveSheet.Shapes.Range(Array("Lock_ONN")).Select  ' x
    Selection.ShapeRange.Height = 28.3464566929

    ' Make OFF lock Big
    ActiveSheet.Shapes.Range(Array("Lock_OFF")).Select  ' x
    Selection.ShapeRange.Height = 46.7716535433

    ' Label
    Range("TLock_LABEL").Select                         ' x
    ActiveCell.FormulaR1C1 = "Auto Update is OFF"
    Selection.HorizontalAlignment = xlLeft
With ActiveCell.Characters(Start:=15, Length:=4).Font
    .FontStyle = "Fett"
    .Size = 10
    .Color = -16776961
End With
    Range("B1").Select

    ' Turn automatic folular updating OFF
    ActiveSheet.EnableCalculation = False

ElseIf ActiveSheet.Name = "5_Angebot" Then

    ' Make ON lock Small
    ActiveSheet.Shapes.Range(Array("Lock_ONN")).Select  ' x
    Selection.ShapeRange.Height = 28.3464566929

    ' Make OFF lock Big
    ActiveSheet.Shapes.Range(Array("Lock_OFF")).Select  ' x
    Selection.ShapeRange.Height = 46.7716535433

    ' Label
    Range("ANLock_LABEL").Select                        ' x
    ActiveCell.FormulaR1C1 = "Auto Update is OFF"
    Selection.HorizontalAlignment = xlLeft
With ActiveCell.Characters(Start:=15, Length:=4).Font
    .FontStyle = "Fett"
    .Size = 10
    .Color = -16776961
End With
    Range("B1").Select

    ' Turn automatic folular updating OFF
    ActiveSheet.EnableCalculation = False
    Range("B1").Select
End If

Application.ScreenUpdating = True  ' see screen updating

End Sub

Followed by copy to create a copy of sheet Angebot (costs for a job offer)

Sub New_Angebot_II()

' *****  Creates copy of sheet 5_Angebot  *****

' x  Defined Cell Names  -  ANVersion ,  ANReplaced

Dim fs   As Worksheet
Dim es   As Worksheet
Dim ns   As Worksheet

Set fs = Sheets("5_Angebot")      ' From WorkSheet
Set es = Sheets("4_Data Form")    ' End on WorkSheet
'       ns = Sheets("5_Angebot I")    ' New WorkSheet  - oooo

Application.ScreenUpdating = False  ' do not see screen updating

' Check if the current Angebot is the first (I)
fs.Select
If Range("ANVersion").Value <> "I" Then           '  x
    MsgBox " Check if Angebot II has already been created " & vbNewLine & _
           "   Choose option to Create Angebot III", , "Check if Angebot II already exists"
    es.Select
    Exit Sub
End If

' Give User a opportunity to stop Copy
If MsgBox("           Angebot I will have its values fixed" & vbNewLine & _
          "             and be renamed as Anbebot II" & vbNewLine & vbNewLine & _
          "  Are you sure you want to create a New Angebot…?", vbQuestion + vbYesNo) <> vbYes Then
    es.Select
Exit Sub
End If

' Select & Copy 5_Angebot
fs.Copy Before:=fs

' Change all formulars to fixed values
ActiveSheet.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False  ' empties the clipboard and clears the memory cache
Range("B1").Select

' Rename sheet as old Angebot
ActiveSheet.Name = Replace$(ActiveSheet.Name, "(2)", "I")
Set ns = ActiveSheet                                      ' New WorkSheet  - oooo
Range("B1").Select

' Remove all the macro Buttons and shapes

'Dim i As Integer
If ActiveSheet.ProtectContents = True Then
    MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & _
           "                          Please resolve these issues and try again."
End If

On Error Resume Next

ActiveSheet.Buttons.Delete

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp

' Protect sheet from updates
    ' Label
    Range("A4").Select                        ' x
    ActiveCell.FormulaR1C1 = "LOCK is ON"
    Selection.HorizontalAlignment = xlRight
With ActiveCell.Characters(Start:=9, Length:=2).Font
    .FontStyle = "Fett"
    .Size = 10
    .Color = -16776961
End With
    Range("B1").Select

'       PROTECT
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

' Go to 5_Angebot change Heading to II
fs.Select
Range("ANVersion").Select          ' x
ActiveCell.FormulaR1C1 = "II"
Range("B1").Select

' Remove EMAILED Heading
Range("ANEmailed").Select          ' x
ActiveCell.FormulaR1C1 = ""
Range("ANEmailDate").Select        ' x
ActiveCell.FormulaR1C1 = ""
Range("B1").Select

' Turn Automatic update ON
Call Turn_AutoUpdate_ONN

' Go Back to 4_Data Form
es.Select
Range("B1").Select

Application.ScreenUpdating = True  ' see screen updating

End Sub