Change Formula's to Values
Hello from Steved
Please how to I put in the below "Change Formula's to Values" Sub Consolidated() 'Working in 97-2007 Dim wb As Workbook Dim Shname As Variant Dim Addr As Variant Dim N As Integer Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Shname = Array("Summary Report") Addr = Array("A-Consolidated_Report") If Val(Application.Version) = 12 Then 'You run Excel 2007 FileExtStr = ".xls": FileFormatNum = 56 Else 'You run Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 End If With Application .ScreenUpdating = False .EnableEvents = False End With TempFilePath = Environ$("temp") & "\" 'Create the new workbooks/Mail it/Delete it For N = LBound(Shname) To UBound(Shname) TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss") ThisWorkbook.Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum On Error Resume Next .SendMail Addr(N), _ "Consolidated Report" On Error Resume Next .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr Next N With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Thankyou. |
Change Formula's to Values
With wb.Worksheets(1).Cells
.Value = .Value End With -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steved" wrote in message ... Hello from Steved Please how to I put in the below "Change Formula's to Values" Sub Consolidated() 'Working in 97-2007 Dim wb As Workbook Dim Shname As Variant Dim Addr As Variant Dim N As Integer Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Shname = Array("Summary Report") Addr = Array("A-Consolidated_Report") If Val(Application.Version) = 12 Then 'You run Excel 2007 FileExtStr = ".xls": FileFormatNum = 56 Else 'You run Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 End If With Application .ScreenUpdating = False .EnableEvents = False End With TempFilePath = Environ$("temp") & "\" 'Create the new workbooks/Mail it/Delete it For N = LBound(Shname) To UBound(Shname) TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss") ThisWorkbook.Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum On Error Resume Next .SendMail Addr(N), _ "Consolidated Report" On Error Resume Next .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr Next N With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Thankyou. |
Change Formula's to Values
Hello Bob
Thanyou just what I required. Steved "Bob Phillips" wrote: With wb.Worksheets(1).Cells .Value = .Value End With -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steved" wrote in message ... Hello from Steved Please how to I put in the below "Change Formula's to Values" Sub Consolidated() 'Working in 97-2007 Dim wb As Workbook Dim Shname As Variant Dim Addr As Variant Dim N As Integer Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Shname = Array("Summary Report") Addr = Array("A-Consolidated_Report") If Val(Application.Version) = 12 Then 'You run Excel 2007 FileExtStr = ".xls": FileFormatNum = 56 Else 'You run Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 End If With Application .ScreenUpdating = False .EnableEvents = False End With TempFilePath = Environ$("temp") & "\" 'Create the new workbooks/Mail it/Delete it For N = LBound(Shname) To UBound(Shname) TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss") ThisWorkbook.Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum On Error Resume Next .SendMail Addr(N), _ "Consolidated Report" On Error Resume Next .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr Next N With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Thankyou. |
All times are GMT +1. The time now is 01:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com