code does not work the way you want.
However, I posted code that does work and so did ohtechie.



The key lines of code are:



strSheetName = ActiveSheet.Range("A1").Value ' get desired sheet  name

Sheets(strSheetName).Copy ' copy  desired sheet into a new
workbook

ActiveWorkbook.SaveAs Filename:=strName ' save  the new workbook

ActiveWorkbook.Close ' close the new workbook



Please try this code. (!)