Change data in temporary created file to value

Hi Folks,

I have a macro that, using formula generated data from a 'Master' file, generates a 'Temporary' file.

What I'd like to do is ensure that the data in the temporary file is changed from the formula generated to values. (I can't change the formula or values in the original file).

This is the code I have that generates the temporary file. Can anyone tell me what code I need to insert where to achieve this please.

Many thanks.

Sub Email_Sheet()

    Dim oApp As Object
    Dim oMail As Object
    Dim LWorkbook As Workbook
    Dim LFileName As String
    
    
    'Turn off screen updating
    Application.ScreenUpdating = False
    
'    Last_Sent Macro
'

'
    Range("I3").Select
    Selection.Copy
    Range("R1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Calculate
    
    'Copy the active worksheet and save to a temporary workbook
    ActiveSheet.Copy
    Set LWorkbook = ActiveWorkbook

    'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name
    On Error Resume Next
    'Delete the file if it already exists
    Kill LFileName
    On Error GoTo 0
    'Save temporary file
    LWorkbook.SaveAs Filename:=Range("I2").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    'Create an Outlook object and new mail message
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    'Set mail attributes (uncomment lines to enter attributes)
    ' In this example, only the attachment is being added to the mail message
    With oMail
        .SentOnBehalfOfName = "To be confirmed"
        .To = "Firstname.lastname@gcompany.co.uk"
'        .cc = "Fred.Bloggs@Company.co.uk"
        .Subject = "Subject header Message Line 1" & Range("I3")
        .body = "Message Line 2"
        .Attachments.Add LWorkbook.FullName
        .Display
    End With

    'Delete the temporary file and close temporary Workbook
    LWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Kill LWorkbook.FullName
    LWorkbook.Close SaveChanges:=False

    'Turn back on screen updating
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
'    Call MarkIDsAfterEmailSent
 
End Sub

Hi there,

This is just a knee-jerk (and untested) response, but try adding the highlighted code to your own version:

Sub Email_Sheet()

    Dim oApp As Object
    Dim oMail As Object
    Dim LWorkbook As Workbook
    Dim LFileName As String
    
    
    'Turn off screen updating
    Application.ScreenUpdating = False
    
'    Last_Sent Macro
'

'
    Range("I3").Select
    Selection.Copy
    Range("R1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Calculate
    
    'Copy the active worksheet and save to a temporary workbook
    ActiveSheet.Copy
    Set LWorkbook = ActiveWorkbook

    With ActiveSheet.UsedRange.Cells
        .Value = .Value
    End With

    'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name

Hope this helps - please let me know how you get on.

Regards,

Greg M

HI Greg,

That's brilliant, it works. (Rep Added - for this alone!)

However, I've also discovered I've got two buttons, with macro's assigned, that also cross reference the original file

Rectangle:Rounded Corners 1
Rectangle:Rounded Corners 2

I don't need them in the copied file. Are you able to come up with a code to delete them please.

Many thanks

Terry

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

To remove the buttons try adding the highlighted code as shown below:

    Calculate
    
    'Copy the active worksheet and save to a temporary workbook
    ActiveSheet.Copy
    Set LWorkbook = ActiveWorkbook

    With ActiveSheet.UsedRange.Cells
        .Value = .Value
    End With

    ActiveSheet.Shapes("Rounded Rectangle 1").Delete
    ActiveSheet.Shapes("Rounded Rectangle 2").Delete

    'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name

Hope this helps - as before, please let me know how you get on.

Best regards,

Greg M