How to copy a worksheet to a new worksheet but as values only?

Question

I copy a worksheet "Costing Sheet" to a new worksheet.

I use this code:

Sub CopyPasteSheetAsValues()
    Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")
    Application.CutCopyMode = False
End Sub

There are formulas in the copied sheet that I do not need in the new worksheet. I would like to copy the worksheet into a new sheet but paste as 'values only'.

I also have the code below that can copy and paste the worksheet but it overrides the current sheet. Instead of a new worksheet with values only, it's the same worksheet as values.

Sub CopyPasteSheet()
    'Copy and Paste Summary Sheet as Values
    Sheets("Costing Sheet").Cells.Copy Before:=Sheets("Comparison Job")
    Sheets("Costing Sheet").Cells.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

I tried to combine the codes but was unsuccessful.

The new sheet is *probably* be called "Costing Sheet (2)". Using `Sheets("Costing Sheet (2)").UsedRange.value = Sheets("Costing Sheet (2)").UsedRange.value ` should convert all the formulas into their resulting values — cybernetic.nomad, Nov 28 '19 at 16:19

score 2 · Accepted Answer · answered Nov 28 '19 at 16:18

2

You can exploit the fact that a sheet becomes the active one when it is copied from another sheet

Sub CopyPasteSheetAsValues()

Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

End Sub

answered Nov 28 '19 at 16:18

SJR

22,986
6
18
26

This worked great! Wondering if it would be possible to do something similar but have range B6:F68 as values only and keep the rest of the new pasted worksheet with the formulas from the copied worksheet. – icalderon Nov 28 '19 at 16:48
Yes just use `range("B6:F68")` instead of `usedrange`. – SJR Nov 28 '19 at 16:51
Thanks for the help! – icalderon Nov 28 '19 at 16:55
This works great, however for some reason some of the formatting is lost. For example superscipt text won't be superscript in the copied workbook. I wonder if there is a way around that? – StefanFFM Jul 28 '23 at 06:45
This https://stackoverflow.com/a/20672092/5629588 (Way 2) keeps all the formatting intact. – StefanFFM Jul 28 '23 at 07:04

score 0 · Answer 2 · answered Nov 28 '19 at 16:18

0

Doing a copy with no destination will create a new workbook with the copied worksheet. Assigning the value of the range to the value of the range will get rid of the formulas.

Dim myNewWB as Workbook

Worksheets("Costing Sheet").Copy
Set myNewWB = ActiveWorkbook
With myNewWB.Worksheets(1)
   .Range(.Cells(1,1),.Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count)).Value= _
   .Range(.Cells(1,1),.Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count)).Value
End With

answered Nov 28 '19 at 16:18

SmileyFtW

326
2
10

Thank you for the feedback. Although I wasn't looking to add the copied sheet to a new workbook this worked great and I will keep it in mind! – icalderon Nov 28 '19 at 16:24
@icalderon - sorry, thought you said you wanted the sheet in a new workbook... I should read more carefully. – SmileyFtW Nov 28 '19 at 16:28

How to copy a worksheet to a new worksheet but as values only?

2 Answers2

Related