Temp work sheets, then do..

[cfer]

Hi,

I have a Workbook, works well with a lot of Macros. I want it to a little more now, with what I want to do, I will need to undo the calculations again after the new macro runs.

I was thinking if I could have a Macro that would save Sheet A and Sheet B to a new file, do what I need, then delete the Temp file, without user intervention.

Any ideas on what sort of code I would need.

Thanks in advance.

Cfer

 

[westconn1]

Set tmpbk = Workbooks.Add
Sheets("sheet A").Copy tmpbk.Sheets(1)
Sheets("sheet B").Copy tmpbk.Sheets(1)
'much other code
tmpbk.Close savechange:=False
set tmpbk = nothing

you should set sheet object for the sheets you want to work with so there is no confusion with sheet names, remember to destroy all objects

 

[cfer]

Hi westconn1,

Thanks for the reply.

I get an error each time, it seems to create a file with a new number each time.
ie Book5, then when run Book6 so on.

Any ideas, as it does not create a file called tmpbk

Thans again..

Cfer

 

[westconn1]

tmpbk would be an object representing the new workbook, whatever the name of the workbook is, as the tmbk is never saved it never has a proper name, just bookX starting from book1 each time excel starts

if you put a breakpoint in the code then in the immediate window type
?tmpbk.name
should print
book?

of course as the book opens and closes you never see the result unless you stop the code part way

 

[cfer]

Hi,

Thanks, will work on it tonight.

Cfer