VBA Copy a range with Formats & Vaues Only

J

jinx

Hi,

Would anyone know how to copy data from one workbook to another and include
the Values and Formatting only ie: not the formulas.

I can copy either the values or the formats but don't seem to be able to
copy both...

Workbooks("a.xls"). _
Worksheets("b"). _
Range("A1:C27").Copy

Workbooks("a.xls"). _
Worksheets("Sheet1").Range("A1:C27").PasteSpecial xlPasteValues

it would be lovely if someone could help with this.
 
M

merjet

Add a line:

Worksheets("Sheet1").Range("A1:C27").PasteSpecial
Paste:=xlPasteFormats

Hth,
Merjet
 
J

JE McGimpsey

One way:

With Workbooks("a.xls")
.Worksheets("b").Range("A1:C27").Copy
With .Worksheets("Sheet1").Range("A1:C27")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting and copy or copy and formatting ?? 2
Cell values to an array, then array transpose to another workbook 8
Copy columns and paste with blank column between 5
Convert A1 value to a sheet name and copy to it 2
Copy FROM - TO Object/Block variable not set 6
macro required.. 1
OnTime Change_Event macro AND copy to sheet2 macro trouble 7
how to copy worksheet to new workbook with values only 3

Top