Copy active sheet to new workbook without formulas

Question

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 VBA Copy a Worksheet to a New Sheet without Formulas with some additions to the code:

Public Sub CopySheetAndRename()
Dim newName As String
 On Error Resume Next
newName = InputBox("Enter the name for the copied worksheet")
 If newName <> "" 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

-- 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!

ActiveSheet.Copy After:=Worksheets(Sheets.Count) doesn't copy a sheet into a new workbook though. — BigBen

Marc Marc · Accepted Answer · 2020-01-21T15:40:16

Avoid using the implicit copying function. Try this out:

Activesheet.Copy
'Entire sheet is now on the clipboard
NewSheet.PasteSpecial Paste:=xlPasteValues
'Paste refers to the method of pasting, you can paste using any of the xlPasteXXX 
types'

NewSheet refers to a sheet object that contains the new sheet. If you need to create a new sheet then simply declare a sheet object and set it to the new sheet Example:

dim ws as worksheet
set ws = Sheets.Add(After:=Sheets.Count)

If you need to add a new workbook that is different code, but google is your friend in that situation!

Copy active sheet to new workbook without formulas

1 Answers