how to copy worksheet to new workbook with values only

[Andrew]

Hello,
I'm trying to save one sheet in one workbook as a new workbook. My
code is shown below. There is one range on the worksheet which has
formulas in it. That range is named "customer_name". I don't want to
copy the formulas. I want only values to go to the new workbook.

The code I have below works, but it is slow. I was wondering if there
was a way to modify the Worksheets("PO").copy command so that it only
copies only values.

thanks in advance for your help.
Andy

Dim wb As Workbook
Worksheets("PO").Copy
Set wb = ActiveWorkbook
wb.SaveAs "NEW_NAME.XLSX"

wb.Worksheets("PO").Range("customer_name").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

wb.Close

 

[GS]

Try...

Sub CopySheetToNewWkb()
Sheets("PO").Copy 'becomes active wkb/wks
With ActiveSheet.UsedRange
.Value = .Value
End With 'ActiveSheet.UsedRange
With ActiveWorkbook
.SaveAs "FullPathAndFilename"
.Close
End With
End Sub

 

[Andrew]

Try...

Sub CopySheetToNewWkb()
  Sheets("PO").Copy 'becomes active wkb/wks
  With ActiveSheet.UsedRange
    .Value = .Value
  End With 'ActiveSheet.UsedRange
  With ActiveWorkbook
    .SaveAs "FullPathAndFilename"
    .Close
  End With
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks. That's quite a bit faster than my code.

Andy

 

[GS]

Andrew submitted this idea :

Thanks. That's quite a bit faster than my code.

Andy

You're very welcome! Always glad to be of help...