Macro to ask user to saveas worksheet

Caveman1964

Board Regular
Joined
Dec 14, 2017
Messages
103
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Code:
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

Board Regular
Joined
Dec 14, 2017
Messages
103
Awe Thank You Ravishankar!!!! I can get back on this project now. You are a lifesaver.
Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top