How to Copy Values and Formats with Paste Special in Excel VBA

Method 1 – Applying an InputBox in VBA Paste Special

Steps:

Go to the Developer tab.
Click on Record Macro.
Set Excel_Paste_Special_1 as the Macro name.
Press OK.

Click the Macros command.
Select the Macro and press Step Into.

Enter the following code on the command module:

Sub Excel_Paste_Special_1()
Dim Copy_Cell As Range, Paste_Cell As Range
xTitleId = "Salary_Sheet"
Set Copy_Cell = Application.Selection
Set Copy_Cell = Application.InputBox("Select Range to Copy :", xTitleId, Copy_Cell.Address, Type:=8)
Set Paste_Cell = Application.InputBox("Paste to any blank cell:", xTitleId, Type:=8)
Copy_Cell.Copy
Paste_Cell.Parent.Activate
Paste_Cell.PasteSpecial xlPasteValuesAndNumberFormats
Paste_Cell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Press F5 to run the code.
A new dialog box will appear. Select the source range on that box.
Press OK.

Another dialog box will appear. Choose a blank range where to paste the copied cells.

All data are copied with values and formats using the VBA Paste Special.

Method 2 – Using xlPasteAllUsingSourceTheme in VBA Paste Special

Steps:

Press Alt+F11 to enter the command module.
Enter the following code on the command module:

Sub Excel_Paste_Special_2()
Range("B4:C9").Copy
Range("E4").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Press F5 to run the code.

This xlPasteAllUsingSourceTheme command copies the exact duplicate of the source data.

Method 3 – Using VBA Variable

Steps:

Go to the command module by pressing Alt+F11.
Enter the following formula in the command module:

Sub Excel_Paste_Special_3()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Range("B4:C9")
Set paste_rng = Range("E4")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Press F5 and run the code.

Method 4 – Using xlPasteValues and xlPasteFormats

Steps:

Go to the command module by clicking Alt+F11.
Enter the following code:

Private Sub Excel_Paste_Special_4()
  Application.ScreenUpdating = False
  Dim source_rng As Worksheet
  Dim paste_rng As Worksheet
  Set source_rng = Worksheets("Data_Set")
  Set paste_rng = Worksheets("Different_Sheet")
  Set Destination = paste_rng.Range("B2")
  source_rng.Range("B2:C9").Copy
   Destination.PasteSpecial Paste:=xlPasteValues
   Destination.PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Press F5 to run the code.

We can see that the data of the Data_Set is copied to Different_Sheet.

Method 5 – Using xlPasteFormats to Paste the Formats Only

Steps:

Go to the command module by pressing Alt+F11.
Enter the following code in the command module:

Sub Excel_Paste_Special_5()
Range("B2:C9").Copy
Range("E2").PasteSpecial xlPasteFormats
End Sub

Press the F5 button and run the code.

Look at the dataset. Only the formats are copied; no values are present here.

Method 6 – Using xlPasteValues to Paste the Values Only

Steps:

Press Alt+F11 to go to the command module.
Enter the following code on the command module:

Sub Excel_Paste_Special_6()
Range("B4:C9").Copy
Range("E4").PasteSpecial xlPasteValues
End Sub

Click on F5 and run the code.

Look at the dataset. Only values are copied here. No formats are copied in this method.

Method 7 – Copying and Pasting All Contents of a Single Cell

Steps:

Press Alt+F11 to go to the command module.
Enter the following code on the module:

Sub Excel_Paste_Special_7()
Range("B4").Copy
Range("E4").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Press the F5 button to run the code.

We can see that a single cell is copied here, not a range.

Method 8 – Copying and Pasting All Attributes of a Certain Column with VBA

Steps:

Press the Alt+F11 to go to the command module.
Enter the following code in the module:

Sub Excel_Paste_Special_8()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Columns("C")
Set paste_rng = Columns("E")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Run the code by pressing F5.

Here, we can see that Column B is copied to Column E.

Method 9 – Coping and Pasting All Attributes of a Row with Excel VBA

Steps:

Click on Alt+F11 to go to the command module.
Enter the following code in the command module:

Sub Excel_Paste_Special_9()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Rows("4")
Set paste_rng = Rows("11")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub