Please add a "Paste Value" Command to this macro.
 

Hi all.

I am new to the world of macros - they're great though.
I have this emailing macro, that emails an active sheet of pivots. I want to
be set it to send as values, so that the pivots cannot be rearranged the
other end, as it retains the facility of beimg able to access the data and
rearrange them.

I have searched and found a few "paste as values" macros, but i am not fully
sure how to add them.
Can someone please alter the below, so the macro will copy as values.

Thanks
Matt
----------

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & 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), _
"SP Booked Meeting Data (Automated)"
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

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook


to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with


"Mattlynn" wrote:

Hi all.

I am new to the world of macros - they're great though.
I have this emailing macro, that emails an active sheet of pivots. I want to
be set it to send as values, so that the pivots cannot be rearranged the
other end, as it retains the facility of beimg able to access the data and
rearrange them.

I have searched and found a few "paste as values" macros, but i am not fully
sure how to add them.
Can someone please alter the below, so the macro will copy as values.

Thanks
Matt
----------

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & 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), _
"SP Booked Meeting Data (Automated)"
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

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Joel - ignore my last message - i am being blind again
Thanks
Matt


Mattlynn wrote:
Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

from
ThisWorkbook.Sheets(Shname(N)).Copy
[quoted text clipped - 14 lines]
End With
End Sub


--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
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


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Hi Joel, is this line still needed?

ThisWorkbook.Sheets(Shname(N)).Copy

It looks like the beginning of a sheet copy statement.

"Joel" wrote:

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
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


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Hi Original code is here (Example 2)
http://www.rondebruin.nl/mail/folder1/mail2.htm

ThisWorkbook.Sheets(Shname(N)).Copy

Yes you need that

It will copy that sheet to a new workbook
Then it paste values
Save
mail
Delete the file

See also
http://www.rondebruin.nl/values.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JLGWhiz" wrote in message ...
Hi Joel, is this line still needed?

ThisWorkbook.Sheets(Shname(N)).Copy

It looks like the beginning of a sheet copy statement.

"Joel" wrote:

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
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


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Hi Ron, Senility is setting in again. I forgot that if before or after is
not specified that it automatically creates a new workbook. I have to use
references more and more these days. :(

"Ron de Bruin" wrote:

Hi Original code is here (Example 2)
http://www.rondebruin.nl/mail/folder1/mail2.htm

ThisWorkbook.Sheets(Shname(N)).Copy

Yes you need that

It will copy that sheet to a new workbook
Then it paste values
Save
mail
Delete the file

See also
http://www.rondebruin.nl/values.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JLGWhiz" wrote in message ...
Hi Joel, is this line still needed?

ThisWorkbook.Sheets(Shname(N)).Copy

It looks like the beginning of a sheet copy statement.

"Joel" wrote:

Sub Mail_test()
'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("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
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 = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
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


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Please add a "Paste Value" Command to this macro.
 

Thanks everyone for your help.
I have now got a great macro, that does just the trick
May thNka
Matt




JLGWhiz wrote:
Hi Ron, Senility is setting in again. I forgot that if before or after is
not specified that it automatically creates a new workbook. I have to use
references more and more these days. :(

Hi Original code is here (Example 2)
http://www.rondebruin.nl/mail/folder1/mail2.htm
[quoted text clipped - 100 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1