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.

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 befo=ActiveWorkbook.Sheets(1)
ActiveWorkbook.SaveAs strName

Set ws = Nothing

End Sub

--
Kevin Backmann


"frageo64" wrote:

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.

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?

"Kevin B" wrote:

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 befo=ActiveWorkbook.Sheets(1)
ActiveWorkbook.SaveAs strName

Set ws = Nothing

End Sub

--
Kevin Backmann


"frageo64" wrote:

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.