Excel VBA to save active tab in folder?

[jehuh]

Hello All,

I'm running into a snag with saving an active tab as a new excel file in a folder with the filename being the same as the tab name.

Any ideas or code that has worked for you?

thanks!

 

[DanteAmor]

The sheet will be saved in the same folder where you have the file with the macro.
Try:

Sub Macro1()
  Dim nm As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  nm = ActiveSheet.Name
  ActiveSheet.Copy
  ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & nm & ".xlsx", xlOpenXMLWorkbook
  ActiveWorkbook.Close
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

If you want another folder change this:

ThisWorkbook.Path

For the folder where you want to save, for example:

ActiveWorkbook.SaveAs "C:\work\files" & "\" & nm & ".xlsx", xlOpenXMLWorkbook

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​