Copy worksheet as a new workbook.

[GedSalter]

I would like to be able to copy a certain worksheet as a new stand alone workbook. The new workbook name should be the number that will be in cell M4 on that particular worksheet.

 

[Fluff]

How about

Sub GedSalter()
   ActiveSheet.Copy
   ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & Range("M4").Value & ".xlsx", 51
End Sub

 

[GedSalter]

Hi Fluff, many thanks for that but I forgot that the file needs to go into another folder called ARCHIVE and it needs to be macro enabled file. When I change the file extention to Xlsm it doesn't like it.

 

[Fluff]

In that case try

ActiveWorkbook.SaveAs "[COLOR=#ff0000]your path here[/COLOR]\" & Range("M4").Value & ".[COLOR=#ff0000]xlsm[/COLOR]", [COLOR=#ff0000]52[/COLOR]

 

[GedSalter]

the path bit is not working for me. The destination folder is on my desktop C:\Desktop\GHR\Quotes\Archives but it doesnt recognise it.

 

[Fluff]

Try

ActiveWorkbook.SaveAs Environ("userprofile") & "\Desktop\GHR\Quotes\Archives\" & Range("M4").Value & ".xlsm", 52

 

[GedSalter]

thanks Fluff. That was brilliant stuff. Worked a treat

 

[Fluff]

You're welcome & thanks for the feedback