VBA - Copy Sheet2 to new workbook without formulas

[zepharoz]

Hi, been trying to figure out why it doesn't work and honestly need the help in case I go insane from this. Sheet1 contains some data but irrelevant and a Button for which runs the code. Sheet2 contains all the formulas, formatting, and heading. Goal is to press button in sheet1, which runs the code that copies sheet2 into a new workbook without any formulas but retains formatting. Code is:

Private Sub CommandButton1_Click()

Sheets("Sheet2").Copy

Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

Thanks in advance.

 

[kevin9999]

Try the following with a copy of your workbook

Private Sub CommandButton1_Click()
    Sheets("Sheet2").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

 

[zepharoz]

Try the following with a copy of your workbook

Private Sub CommandButton1_Click()
    Sheets("Sheet2").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

I don't know what magic you used, but it works. Can you tell me why my code didn't work or why yours did? Thanks a million.

 

[kevin9999]

Not sure why yours didn't work - it does a very similar thing in a different way (although yours selects the entire sheet v just the used range).