macro to save as

[Guest]

I want to create a macro to get the contents of a cell of a specific sheet,
open save as dialog and using the contents of that cell as the new filename,
to save only the current sheet.

 

[Guest]

Try something along these lines. Press Alt+F11 and select INSERT from the
menu and select MODULE:

Sub CopyWS()

Dim strName As String
Dim ws As Worksheet

'Change range to the cell that contains the new filename

strName = Range("A1").Value
Set ws = ThisWorkbook.Sheets(1)
Application.Workbooks.Add
ws.Copy before:=ActiveWorkbook.Sheets(1)
ActiveWorkbook.SaveAs strName

Set ws = Nothing

End Sub

 

[Guest]

Kevin thank you very much for your help. Can I take it a lilltle bit further
and instead of copying a sheet can I copy a range of cells?