macro to save active worksheet as new workbook, name it and close it.

[mdbarbee]

Can you create a macro that will copy the active tab/worksheet in a workbook and save it as a new workbook, and close that new workbook. The new workbook should be named whatever the tab/worksheet was named in the original workbook, and the new worksheet should have that same name on its tab, too. The new workbook should be saved in the same location as the original workbook (or it can be saved on my desktop if that makes it any easier). I am using Excel2016.

I have this macro below that pretty much does what I need, except it copies each (and every) worksheet in the original workbook and makes a new workbook for each worksheet. I only need to copy one of the worksheets, not all of them. Here’s that macro in case it can be easily modified:

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

ws.Copy
​

ActiveWorkbook.SaveAs Filename:=ws.Name & ".xlsx"
​

ActiveWorkbook.Close
​

Next ws
End Sub

Thank you for any assistance you can offer.

 

[Fluff]

Hi & welcome to MrExcel
Try

Sub test()
ActiveSheet.Copy
ActiveWorkbook.SaveAs ws.Name & ".xlsx", 51
ActiveWorkbook.Close False
End Sub

 

[mdbarbee]

I don't know how/why, but I got this to work. I just kept playing around with yours until something worked:

Sub test()
Dim WS As Worksheet
Set WS = ActiveSheet
 WS.Copy
 ActiveWorkbook.SaveAs Filename:=WS.Name & ".xlsx"
 ActiveWorkbook.Close False
End Sub

Thank you much!

 

[Fluff]

Glad it's sorted & thanks for the feedback