i have a workbook that opens with scheduler and pulls data in, then sends to
printer, i have a macro that i call that coverts all formulas to values,
saves to a new file and then excel closes, my problem is that when i open the
saved filed that it created, it will run macro to convert values, print and
resave. is there something i can change so that the saved file will contain
only values and no modules to run?

You can use code at Chip Pearson's site to remove the modules:
http://cpearson.com/excel/vbe.htm

But you may find just creating a new workbook, then looping through each
worksheet and copying the data and paste special|values into that new workbook
is easier.

Mark J wrote:

i have a workbook that opens with scheduler and pulls data in, then sends to
printer, i have a macro that i call that coverts all formulas to values,
saves to a new file and then excel closes, my problem is that when i open the
saved filed that it created, it will run macro to convert values, print and
resave. is there something i can change so that the saved file will contain
only values and no modules to run?

--

Dave Peterson

Nope.

but it would look like:

dim wks as worksheet
dim newwkbk as workbook
dim newwks as worksheet
set newwkbk = workbooks.add(1)
newwkbk.worksheets(1).name = "deletemelater"
for each wks in activeworkbook.worksheets
set newwks = newwkbk.worksheets.add
newwks.name = wks.name
wks.cells.copy
newwks.range("a1").pastespecial paste:=xlpastevalues
'paste formatting, adjust row heights/columnwidths and tidy up???
next wks

application.displayalerts = false
newwkbk.worksheets("deletemelater").delete
application.displayalerts = true

newwkbk.saveas filename:=whateveryouwant,fileformat:=xlworkbookno rmal

Watch for typos--I composed in the email message.


Mark J wrote:

thanks for the info, do you have a example of that solution?

"Dave Peterson" wrote:

You can use code at Chip Pearson's site to remove the modules:
http://cpearson.com/excel/vbe.htm

But you may find just creating a new workbook, then looping through each
worksheet and copying the data and paste special|values into that new workbook
is easier.

Mark J wrote:

i have a workbook that opens with scheduler and pulls data in, then sends to
printer, i have a macro that i call that coverts all formulas to values,
saves to a new file and then excel closes, my problem is that when i open the
saved filed that it created, it will run macro to convert values, print and
resave. is there something i can change so that the saved file will contain
only values and no modules to run?

--

Dave Peterson


--

Dave Peterson