save activesheet as a new workbook

kwagner1

Active Member
Joined
Jun 10, 2003
Messages
445
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,
You can record a macro for this...
you will get something like
Code:
    Sheets("sheet1").Select
    Sheets("sheet1").Copy
make it
Code:
ActiveSheet.Copy
kind regards,
Erik
 

kwagner1

Active Member
Joined
Jun 10, 2003
Messages
445
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:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
If you omit the Before/After argument when you copy a sheet then a new workbook is created, with only that sheet.
Code:
ActiveSheet.Copy

Set wbNew = ActiveWorkbook

wbNew.SaveAs ThisWorkbook.Path & "\testken"
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,027
Members
410,647
Latest member
bernardazar
Top