how to copy worksheet to new workbook with values only
 

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

how to copy worksheet to new workbook with values only
 

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

how to copy worksheet to new workbook with values only
 

On Jul 7, 12:20*pm, GS wrote:
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

how to copy worksheet to new workbook with values only
 

Andrew submitted this idea :
On Jul 7, 12:20*pm, GS wrote:
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

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

--
Garry

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