Copying to a new workbook??

[direktoren]

Hi

I'm running a macro that sorts, filters and formats some source data - until now my results have shown in the same workbook as the macro is running from, but I've found it more handy (and a lot of harddrive space saving), if my results appeared in a new workbook.

I used the macro recorder to try to get a clue of what to do, but it only gave me more questions;

I'm using a commandbutton to execute the code. The first time it is run it should generate a new workbook and paste the results in there, however the second time it is run it shouldn't create a new workbook (since it has already been created), but merely copy the results into two new sheets. Something like;

if not workbook("book1") exists then
workbook.add
'code
else
'code
end if

any ideas? bet there is a dead easy solution

[Ecniv]

you have a sheets collection in the workbook.
So you need to add a new sheet (holding a reference variable to it) then copy or do whatever to paste the results into that new sheet.

dim wrk as workbook
dim sht as worksheet, shtnew as worksheet

set wrk = activeworkbook
set sht = wrk.sheets(1)
set shtnew = wrk.sheets.add 'check the help/intellisense for options
sht.range("A1:b3").copy
shtnew.cells(1,1).paste

set sht = nothing
set shtnew = nothing
set wrk = nothing

The above code is guidelines only - probably won't run, but should give you an idea.

[direktoren]

Thanks, but I'm well aware how to add new sheets to an existing workbook.

What I want is to add a new workbook and then copy some of the sheets from the existing workbook into the new workbook - only once I have made a new workbook I don't want to make another one, so the second time I run the code I only want to add new sheets to the new workbook and then paste my results into these... Something like the fictive code I wrote in my post.

[westconn1]

Dim mynewworkbook As Workbook
Set mynewworkbook = Application.Workbooks.Add

this will create a new workbook ,book1

pete

[direktoren]

Thanks, but I know how to add a workbook as well.

What I need is something like;

if not book1 exists then 'This is the part that bothers me!
workbooks.add 'adding book1
'copy results to book1
else
'copy results to book1 - which I'm now certain already exist
end if

Nicoaj

[westconn1]

your workbook is an xls file

myfile = "myworkbook.xls"
if  dir(myfile) = "" then 
    workbooks.add
  else
     'open myfile
end if
 
'your code

you would need to save book1 as myfile after adding it so it will get the same workbook name, for the rest of the code

pete