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.

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" <(E-Mail Removed)> wrote in message
news:49E5EF0C-0FA3-4A7D-A7F4-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
> news:49E5EF0C-0FA3-4A7D-A7F4-(E-Mail Removed)...
> > 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.
>
>
>