Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Please add a "Paste Value" Command to this macro.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default 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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to have a macro simply issue the "find" command or "control f: Charles Adams Excel Programming 3 February 6th 09 06:34 PM
How do I set my "Insert" key as a paste command shortcut in Excel Andrew Ball Excel Discussion (Misc queries) 7 June 20th 08 12:00 PM
Is there an equivalent of Lotus 123's "Paste visible" command? AJ Excel Discussion (Misc queries) 6 March 16th 06 09:21 AM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 10:01 PM
I want to combine a "match" command with a copy and paste command. alomega Excel Programming 1 February 9th 05 05:52 PM


All times are GMT +1. The time now is 03:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement