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.

Apply InputBox for Paste Special Values and Formats in Excel VBA

  • 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

Apply InputBox for Paste Special Values and Formats in Excel VBA

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

Apply InputBox for Paste Special Values and Formats in Excel VBA

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

Apply InputBox for Paste Special Values and Formats in Excel VBA

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

 

Read More: How to Use VBA PasteSpecial to Keep Source Formatting in Excel


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

Insert Cell Range with xlPasteAllUsingSourceTheme to Paste Special Values and Formats in VBA

  • Press F5 to run the code.

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

Read More: How to Use VBA PasteSpecial for Formulas and Formats in Excel


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

Use Variable to Insert Range and Paste Special Values and Formats

  • 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

Use xlPasteValues and xlPasteFormats to Copy Values and Formats in Other Sheet Keeping the Formats Unchanged

  • 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

Use xlPasteFormats to Paste the Formats Only

  • 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

Use xlPasteValues to Paste the Values Only

  • Click on F5 and run the code.

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

Read More: How to Use VBA to Paste Values Only with No Formatting in Excel


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

Copy and Paste Contents of a Single Cell in VBA

  • Press the F5 button to run the code.

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

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell


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

Copy and Paste Attributes of a Certain Column in VBA

  • Run the code by pressing F5.

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

Read More: Excel VBA to Copy Only Values to Destination


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

Copy and Paste Attributes of a Row in Excel VBA

  • Run the code by pressing F5.

Here, Row 4 is copied to Row 11.


Download the Practice Workbook

Download this workbook to practice.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo