For Ron Bruin Please
Hello Ron from Steved
Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub |
I'm not Ron, but you could try this:
make this portion: For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy ... look like: For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy with activesheet.usedrange .value = .value end with ... Steved wrote: Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub -- Dave Peterson |
Hi Steved
http://www.rondebruin.nl/mail/folder1/mail2.htm Dave already posted a good solution I see If you click on the tip link on the page above you can see a other solution also. http://www.rondebruin.nl/mail/tips1.htm Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub |
Thankyou Very Much.
-----Original Message----- I'm not Ron, but you could try this: make this portion: For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy ... look like: For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy with activesheet.usedrange .value = .value end with ... Steved wrote: Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3- Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub -- Dave Peterson . |
I don't understand this portion:
Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you I bet you're not writing about a simple formula link??? Ron de Bruin wrote: Hi Steved http://www.rondebruin.nl/mail/folder1/mail2.htm Dave already posted a good solution I see If you click on the tip link on the page above you can see a other solution also. http://www.rondebruin.nl/mail/tips1.htm Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub -- Dave Peterson |
Hi Dave
You are correct A few weeks back a OP have problems with the value code When I send him a example with PasteSpecial it was working. In his thank you note he say this I forgot to take the comment tags out.. Opps. both work great. Sorry I am getting old <g -- Regards Ron de Bruin http://www.rondebruin.nl "Dave Peterson" wrote in message ... I don't understand this portion: Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you I bet you're not writing about a simple formula link??? Ron de Bruin wrote: Hi Steved http://www.rondebruin.nl/mail/folder1/mail2.htm Dave already posted a good solution I see If you click on the tip link on the page above you can see a other solution also. http://www.rondebruin.nl/mail/tips1.htm Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub -- Dave Peterson |
The only difference I've seen between .value = .value and copy|paste
special|values is when you have cells that have character by character formatting. the .value = .value changes the cell's format to match the first character in the cell (IIRC). the paste special|values keeps that formatting in tact. === http://www.aarp.org ????? <gd&r Ron de Bruin wrote: Hi Dave You are correct A few weeks back a OP have problems with the value code When I send him a example with PasteSpecial it was working. In his thank you note he say this I forgot to take the comment tags out.. Opps. both work great. Sorry I am getting old <g -- Regards Ron de Bruin http://www.rondebruin.nl "Dave Peterson" wrote in message ... I don't understand this portion: Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you I bet you're not writing about a simple formula link??? Ron de Bruin wrote: Hi Steved http://www.rondebruin.nl/mail/folder1/mail2.htm Dave already posted a good solution I see If you click on the tip link on the page above you can see a other solution also. http://www.rondebruin.nl/mail/tips1.htm Dave's solution will not make values of external links (to other workbooks) Maybe this is no problem for you -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved Ok is it possible with the below please to hightlight the whole page and change all the formulas to a value, so I am asking please is their a formula you can add to the below to achieve this. Thankyou. Sub Mail_test() Dim wb As Workbook Dim strdate As String Dim Shname As Variant Dim Addr As Variant Dim N As Integer strdate = Format(Now, "dd-mm-yy h-mm-ss") Shname = Array("1-City", "2-Roskill", "3-Papakura", "4- Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure") Addr = Array("1-Depot", "2-Depot", "4-Depot", "4- Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot") Application.ScreenUpdating = False For N = LBound(Shname) To UBound(Shname) Sheets(Shname(N)).Copy Set wb = ActiveWorkbook With wb .SaveAs "Sheet " & Shname(N) _ & " " & strdate & ".xls" .SendMail Addr(N), _ "Depot Annulments" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Next N Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com