title: Copy active sheet to new workbook without formulas
body: <p>I would like to copy the active sheet (notwithstanding the name of the sheet) to a new WorkBook to be saved without a formula. A copy of the sheet to a new sheet is working according to <a href="https://stackoverflow.com/questions/59773278/vba-copy-a-worksheet-to-a-new-sheet-without-formulas">VBA Copy a Worksheet to a New Sheet without Formulas</a> with some additions to the code:</p>
  
  <pre><code>Public Sub CopySheetAndRename()
  Dim newName As String
   On Error Resume Next
  newName = InputBox("Enter the name for the copied worksheet")
   If newName &lt;&gt; "" Then
      ActiveSheet.Copy After:=Worksheets(Sheets.Count)
      On Error Resume Next
      ActiveSheet.Name = newName
      Worksheets(newName).UsedRange.Value = Worksheets(newName).UsedRange.Value
  End If
  End Sub
  </code></pre>
  
  <p>-- Then I tried to add the dialogbox 
      Application.Dialogs(xlDialogSaveAs).Show
  before "end if" and change "ActiveSheet.Name" to "ThisWorkbook.ActiveSheet.Name" but it does not work. (Or at least it copies the sheet into a new workbook but does not transfer the values). Any ideas on how to run the code? Thank you in advance!</p>