Macro for exporting individual sheet to new workbook

[markdwalls]

Hi All,

I was wondering if there is a way you could write a macro so that one sheet, when a button is pressed, could be exported to a new workbook with values only as the formulas within the sheet are linked to other sheets.
I was also wondering if i could automatically name the file depending on a certain cell e.g. Filename="X"&A1&"X"

I am not very good with macros so any help would be greatly appreciated.

Kind regards,

Mark

 

[Fluff]

How about

Sub markdwalls()
   Sheets("Test").Copy
   With ActiveWorkbook
      With .Sheets("Test").UsedRange
         .Value = .Value
      End With
      .SaveAs ThisWorkbook.Path & "\" & Range("A1").Value, 52
   End With
End Sub

Change sheet name to suit

 

[markdwalls]

Thank you for your reply. This works quite well. Is there a way i can save it manually to a place of my choosing instead of it automatically saving?

Thanks in advance

 

[Fluff]

OK, how about

Sub markdwalls()
   Dim FullPth As String
   
   Sheets("Test").Copy
   With ActiveWorkbook
      With .Sheets("Test").UsedRange
         .Value = .Value
         FullPth = Application.GetSaveAsFilename(.Range("A1").Value)
      End With
      .SaveAs FullPth & ".xlsm", 52
   End With
End Sub

 

[markdwalls]

That works perfectly! Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.