Macro: create Copy of worksheet - paste special - save file based on cell reference

[JrExcelTor]

Hi All,

I am trying to complete a macro that will copy a sheet to a new workbook, copy and paste special as values, remove the original macro, name and save the sheet/workbook based on a cell reference, and finally share the file as email attachment.

What I have has been recorded as I do not know how to write VBA. The part I am struggling with is how to name the new worksheet and save the workbook based on cell reference C2 (Prospective Client Name).


I don't know if file path is needed but here is where I want the file saved:

--> /Users/davidsmith/Desktop/OA/Quotes

Here is what my macro looks like now:
THANKS IN ADVANCE!!!

Sub New_Quote()
'
' New_Quote Macro
'


'
Sheets("Client ROI").Select
ExecuteExcel4Macro "WINDOW.SIZE(398,85,"""")"
ExecuteExcel4Macro "WINDOW.MOVE(2,-42,"""")"
Sheets("Client ROI").Copy
ActiveSheet.Shapes.Range(Array("Bevel 2")).Select
Selection.Cut
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Client ROI").Select
Application.CutCopyMode = False
Sheets("Client ROI").Name = "CSS"
Range("M16").Select
Application.Dialogs(xlDialogSendMail).Show
End Sub