Macro code to save sheet as a separate file to a specific location

[Manny Singh]

Hi All,

Could you help writing a code for attached sheet to below requirement, much appreciated.

When I execute macro, it will save sheet "Comp Reg" as a separate file, file name to be same as cell M2 of sheet Comp Reg.

File location in the desktop ( I will update the location).

Thanks, Manny

 

[Artik]

More or less something like that:

Sub SaveSheet()
  Dim strPath  As String

  Application.ScreenUpdating = False
  Application.EnableEvents = False

  With Worksheets("Comp Reg")
  strPath = CreateObject("Wscript.Shell").SpecialFolders(4) & Application.PathSeparator & _
  .Range("M2").Value
  .Copy
  End With
  
  ActiveWorkbook.SaveAs strPath, xlOpenXMLWorkbook

  ActiveWorkbook.Close False
  Application.EnableEvents = True
End Sub

Artik