Is it possiblae to save active sheet as workbook?

[coolcarls]

I want a macro or module to save the active sheet only as a workbook. Can this be done?

Also, whiole I'm here, what's the difference/bennift between a macro and a module?

 

[dsi]

A macro is a program and a module is the workspace that contains the macro. You can have several macros and functions in a single module. Putting macros in modules is a plus if you want to access them by name. For example, if you put a macro in the worksheet object, you will have to run sheet1.YourMacro instead of YourMacro. Also, if you put your code into a module, you can export the .bas file and import it into other applications.

Tweak this as needed:

Sub CopySheet()
    Dim sName1 As String
    Dim sName2 As String
    Dim sShtName As String
    sName1 = ActiveWorkbook.Name
    sShtName = ActiveSheet.Name
    Workbooks.Add
    sName2 = ActiveWorkbook.Name
    Windows(sName1).Activate
    Sheets(sShtName).Select
    Sheets(sShtName).Copy Before:=Workbooks(sName2).Sheets(1)
End Sub

 

[coolcarls]

I think I may be seeing the light!! Thanks so much for the help,code & explanation

Carl

 

[acron]

You can shorten things by taking advantage of the fact that if you do specify a Before of After sheet in the sheet copy command, a new workbook is created and becomes the active workbook. So

Sub NewWorkbookSave()
ActiveSheet.Copy
ActiveWorkbook.SaveAs ("NewWBName.xls&quot
End Sub

should copy the active sheet and save it as a new workbook with in this case the name NewWBName.xls.

AC