PC Review


Reply
Thread Tools Rate Thread

how to copy worksheet to new workbook with values only

 
 
Andrew
Guest
Posts: n/a
 
      7th Jul 2011
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
 
Reply With Quote
 
 
 
 
GS
Guest
Posts: n/a
 
      7th Jul 2011
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


 
Reply With Quote
 
Andrew
Guest
Posts: n/a
 
      7th Jul 2011
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
 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      8th Jul 2011
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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy values of worksheet a to workbook b Nev Microsoft Excel Misc 5 8th Apr 2009 09:35 PM
Copy Values From Worksheet to another Worksheet same Workbook =?Utf-8?B?Sm9lIEsu?= Microsoft Excel Programming 1 6th Oct 2007 08:45 PM
Copy Values from WorkSheet back to Another Workbook Replacing Values in Worksheet bobwilson Microsoft Excel Programming 0 3rd Apr 2006 09:50 PM
Copy worksheet values and formats into another workbook bishwino Microsoft Excel Programming 0 28th Nov 2005 05:19 PM
Copy worksheet values and formats into another workbook =?Utf-8?B?Umlja19XZW5kdA==?= Microsoft Excel Programming 1 20th Oct 2004 08:14 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:07 PM.