VBA Copy values only onto new workbook
 

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 !!

VBA Copy values only onto new workbook
 

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 !!