Prev: Setting length of data in Excel
Next: copying editing cells
From: Mara on 4 May 2010 20:56
Hi,

I've been successfully using Ron de Bruins Mail An Active sheet macro.
I however now need to modify the macro so it either:
1. copys Macro Functions onto the new workbook or
2. it pastes Values and Formats Only onto the new workbook opposed to "Set
Destwb = ActiveWorkbook".
The reason being, I have macro formulas now doing calculations on the page,
so when the new workbook is created the formulas results in #NAME?. I would
prefer not to copy and paste values only over the main working document as
this would loose the formulas that the users require for when they use it
again.

Here's the start of the code that I'm hoping someone can modify for me:

Sub Mail_ActiveSheet_xx() 's()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
ActiveSheet.Copy

'It's here when I need it to paste values and formats only on the new
workbook. Or alternatively, copy the Macro Functions aswel so the formulas
work.

Set Destwb = ActiveWorkbook

Appreciate anyones help !!

From: Mara on 4 May 2010 21:43
Hi,
Someone in my office has given me the resolution.
For those interested:
In the Macro Function, the first two lines need to read:

Public Function ewronginfo(cell1, cell2 ....)
Application.Volatile (True)

"Mara" wrote:

> Hi,
>
> I've been successfully using Ron de Bruins Mail An Active sheet macro.
> I however now need to modify the macro so it either:
> 1. copys Macro Functions onto the new workbook or
> 2. it pastes Values and Formats Only onto the new workbook opposed to "Set
> Destwb = ActiveWorkbook".
> The reason being, I have macro formulas now doing calculations on the page,
> so when the new workbook is created the formulas results in #NAME?. I would
> prefer not to copy and paste values only over the main working document as
> this would loose the formulas that the users require for when they use it
> again.
>
> Here's the start of the code that I'm hoping someone can modify for me:
>
> Sub Mail_ActiveSheet_xx() 's()
> 'Working in 97-2007
> Dim FileExtStr As String
> Dim FileFormatNum As Long
> Dim Sourcewb As Workbook
> Dim Destwb As Workbook
> Dim TempFilePath As String
> Dim TempFileName As String
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> Set Sourcewb = ActiveWorkbook
>
> 'Copy the sheet to a new workbook
> ActiveSheet.Copy
>
> 'It's here when I need it to paste values and formats only on the new
> workbook. Or alternatively, copy the Macro Functions aswel so the formulas
> work.
>
> Set Destwb = ActiveWorkbook
>
> Appreciate anyones help !!
>
 | 
Pages: 1
Prev: Setting length of data in Excel
Next: copying editing cells