macro to save as

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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?
 
Back
Top