Copy Sheet - Paste Values
 

Hello, I hope someone can help me.

I am trying to copy an ENTIRE sheet from one workbook to
another. But, I want the copy to be pasted as "values". I
also need all the formatting, column widths, etc. to carry
over to the copy.

I can't get it to work using something like the following:

Sheets(argSourceSheet).Copy After:=Workbooks _
(argDestFile).Sheets(argPosition).PasteSpecial _
(Paste:=xlPasteValues)

I also tried something like the following to convert the
copied sheet to values after copying, but this fails also:

Workbooks(argNewFile).Sheets(argSheetName).UsedRan ge.Copy
Workbooks(argNewFile).Sheets(argSheetName).PasteSp ecial _
Paste:=xlPasteValues

I couldn't find a solution in "Help" on this.

1. Is there a way to do this in one line of code?

2. If not, how can I accomplish this most efficiently?

3. Your example code would be the best thing I could get.

Thanks in advance for your example code.

Copy Sheet - Paste Values
 

Hi Stone,

I had a posed a similar question not so long ago, the responses
received led me to the following code (amended to use your details): -


Workbooks(argNewFile).WorkSheets(argSheetName).Cel ls.Copy
With Workbooks(argNewFile).Sheets(argSheetName).
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Wit

--
Message posted from http://www.ExcelForum.com