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

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

On Jul 7, 12:20*pm, GS <g...@somewhere.net> 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

Andrew submitted this idea :
> On Jul 7, 12:20*pm, GS <g...@somewhere.net> 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