Macro to ask user to saveas worksheet

[Caveman1964]

Hi,
Looking for a macro to be able to save only one worksheet of the workbook.
For example, msgbox asks user to saveas H:\Jobs\ and then saves sheet 5 as an excel workbook itself.

and if possible, it uses the value of cell b2 as the name of file."H:\Jobs\B2 value
I tried but I have a mess.
Thanks ahead of time.

 

[ravishankar]

Sub Caveman()
Sheets("Sheet5").UsedRange.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="H:\jobs\" & Cells(2, 2), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False ' include extension like .xlsx in cell B2
           MsgBox "complete"
End Sub

 

[Caveman1964]

Awe Thank You Ravishankar!!!! I can get back on this project now. You are a lifesaver.
Much appreciated.