Using Copy Destination and Paste Special to keep formatting

Question

This is the line of code that I need to, when copying the cells, keep the formatting in the destination workbook. I'm not sure how I would incorporate something like "PasteSpecial Paste:=xlPasteValues" into it.

wbkOut.Worksheets(1).Range("E44:E2000").Copy Destination:=wbkVer.Worksheets("Metabuild").Range("A" & wbkVer.Worksheets("Metabuild").Range("A65536").End(xlUp).Row + 1)

If you know the address of the destination range then you can also do this (For example) wbkVer.Worksheets("Metabuild").Range("A44:A2000").Value = wbkOut.Worksheets(1).Range("E44:E2000").Value — Siddharth Rout

Scott Holtzman Scott Holtzman · Accepted Answer · 2012-12-27T18:56:43

Ben was right. Since he hasn't shown you how, I will.

wbkOut.Worksheets(1).Range("E44:E2000").Copy 

With wbkVer.Worksheets("Metabuild")
  .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With

Another thing you may want to add in is a check to make sure there are enough rows at the bottom of your sheet to handle the row count of the copied range, if you may need it. If there aren't enough rows, the code will throw an error.

To do that, the code will look something like this:

Option Explicit

Dim rngCopy as Range, lngCopy as Long

Set rngCopy = wkbOut.Worksheets(1).Range("E44:E2000")
lngCopy = rngCopy.Rows
rngCopy.Copy

With wbkVer.Worksheets("Metabuild")

    Dim rngCheck as Range
    Set rngCheck = .Range(.Range("A" & .Rows.Count).End(xlup).Offset(1),.Range("A" & .Rows.Count)

    If rngCheck.Rows >= lngCopy Then

       .Range("A" & .Rows.Count).End(xluP).Offset(1).PasteSpecial xlPasteValues

    Else

       Msgbox "Not enough space!"

   End If

End With

Using Copy Destination and Paste Special to keep formatting

1 Answers