Copy And Paste Special To Preserve Target Formatting
A very common practice in Excel formatting applications is to use a target worksheet that is already properly formatted to receive data from another workbook or worksheet. To illustrate the difference between a "copy and paste" and a "copy and paste special values only", the statement immediately below will do a copy and "destructive" paste... meaning that it will override the target format:
Range(shtSheet1.Cells(1, 1), shtSheet1.Cells(6, 1)).Copy shtSheet3.Cells(10, 1)To preserve the formatting of the target worksheet, it is necessary to paste only the values and not the formats. The example below shows how to copy and "paste special (values only)" in a variety of ways:
Program Code
Option Explicit
Public Sub TestPasteSpecial()
Dim shtSheet1 As Worksheet
Dim shtSheet2 As Worksheet
Dim shtSheet3 As Worksheet
' ***************************************************************************
' Set Worksheet Variables
' ***************************************************************************
Set shtSheet1 = Sheets("Sheet1")
Set shtSheet2 = Sheets("Sheet2")
Set shtSheet3 = Sheets("Sheet3")
' ***************************************************************************
' Select A Sheet Using Either Method
' ***************************************************************************
Sheets("Sheet2").Select
' Or
shtSheet2.Select
' ***************************************************************************
' Paste Special Method 1 = Non-ActiveSheet Cells From Sheet1 To Sheet3
' ***************************************************************************
Range(shtSheet1.Cells(1, 1), shtSheet1.Cells(6, 1)).Copy
shtSheet3.Cells(10, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' ********************************************************************************
' Paste Special Method 2 = Copy ActiveSheet Selection And Paste To Different Sheet
' ********************************************************************************
shtSheet1.Select
Range(Cells(1, 2), Cells(6, 3)).Select
Selection.Copy
Sheets("Sheet2").Cells(20, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' ***************************************************************************
' Paste Special Method 3 = Copy, Select New Sheet, Paste Selection
' ***************************************************************************
Sheets("Sheet1").Select
Range(Cells(1, 3), Cells(6, 3)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Option Explicit
' ******************************************
' How To Select Discontinuous Cells
' ******************************************
Public Sub SelectMultipleCells()
Dim wksSheet1 As Worksheet
Dim strRange As String
Set wksSheet1 = Sheets("Sheet1")
wksSheet1.Range("A2,B3,C4,E5").Select
strRange = "A2,B3,C4,D6,E8"
wksSheet1.Range(strRange).Select
End Sub