save activesheet as a new workbook

[kwagner1]

I have a workbook that has multiple worksheets. As my code processes the workbook i move from sheet to sheet. Is there a way to save JUST the activesheet to a new workbook (i.e. "save as") - or do i have to have the code open a new workbook, copy, paste the worksheet to the new workbook and then close it. (and yes, i'm looking for code to do it all...)

Thanks!

 

[erik.van.geit]

Hi,
You can record a macro for this...
you will get something like

    Sheets("sheet1").Select
    Sheets("sheet1").Copy

make it

ActiveSheet.Copy

kind regards,
Erik

 

[kwagner1]

Erik, your code looks like it will select another sheet in the book and then copy it. I want to select another sheet in the book and then save that sheet to another file. Here's my code so far:

Sheets(2).Select
ActiveSheet.SaveAs ThisWorkbook.Path & "\" & "testken"

The problem is the new workbook called "testken.xls" has all the sheets from the original workbook. i simply want the 1 sheet saved to the new "testken.xls" file.

 

[Norie]

If you omit the Before/After argument when you copy a sheet then a new workbook is created, with only that sheet.

ActiveSheet.Copy

Set wbNew = ActiveWorkbook

wbNew.SaveAs ThisWorkbook.Path & "\testken"

 

[kwagner1]

Nice! thanks Norie