Paste Special using Range in VBA

[MarkVMcCullagh]

How do I modify the following line within VBA to paste special values, formats and column widths?

WS.Range("d22:t42").Copy Destination:=Sheets("Consolidated").Range("A65536").End(xlUp).Offset(1, 0)

Thanks in Advance

 

[Damon Ostrander]

Hi Mark,

To paste special you must switch to the Copy and PasteSpecial methods separately. In addition, to paste values and formats (assuming you mean all cell formats and not just numberformats) you must paste values and formats separately. I believe this will do it:

WS.Range("d22:t42").Copy
With Sheets("Consolidated").Range("A65536").End(xlUp).Offset(1)
   .PasteSpecial xlPasteValues
   .PasteSpecial xlPasteFormats
End With

But regarding column widths, you can't do this with a range of cells (as in your example) that is not a complete column because column widths apply to entire columns only. If you want to set the column width of your destination columns to that of your source columns the following code could be used:

   For Each col In WS.Columns("D:T")
      Sheets("Consolidated").Columns(col.Column-3).ColumnWidth = col.ColumnWidth
   Next col

Damon

 

[Norie]

You could also use Paste Special>Column Widths.

Adding to Damon's code.

WS.Range("d22:t42").Copy 
With Sheets("Consolidated").Range("A65536").End(xlUp).Offset(1) 
   .PasteSpecial xlPasteValues 
   .PasteSpecial xlPasteFormats 
   .PasteSpecial Paste:=xlColumnWidths
End With

Note I think this option is only available from Excel 2000 onwards.

Could be wrong though.

 

[MarkVMcCullagh]

Thanks Guys. I'll be trying them out tomorrow.