How to Use VBA PasteSpecial to Keep Source Formatting in Excel

Get FREE Advanced Excel Exercises with Solutions!

However, there are situations when we want to duplicate more than simply text. We can copy all of Excel’s capabilities, such as conditional formatting, validation of data, data-type formatting, and aesthetic features like borders and cell colors. Perhaps you admire your coworker’s color scheme but require static data, or perhaps the formulas are fantastic but you dislike the color scheme. In this tutorial. We’ll show the VBA PasteSpecial and keep the source formatting in Excel.


How to Apply VBA PasteSpecial and Keep Source Formatting in Excel: 4 Easy Examples

We’ll demonstrate four examples of how to use the paste special in VBA while retaining the source format in the sections below. Here is a sample data set that we will use to copy and paste.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

1. Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

First and foremost. We’ll use the box to select a range and then paste it into another cell. To do so, follow the steps below.

  • Firstly, press  Alt  F11   to start a Macro.
  • Then, click on the Insert, choose the Module option.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

  • Paste the following VBA code.
Sub PasteSpecialKeepFormat()
'declare CopyCell and PasteCell as range variable
Dim CopyCell As Range, PasteCell As Range
'Give a name to the Input Box
xTitleId = "ExcelDemy"
'Set copy range will be selected from the worksheet
Set CopyCell = Application.Selection
'Set a Input Box to take a range to copy from
Set CopyCell = Application.InputBox("Select Range to Copy :", xTitleId, CopyCell.Address, Type:=8)
'Set a Input Box to take a range to paste to
Set PasteCell = Application.InputBox("Paste to any blank cell:", xTitleId, Type:=8)
'Command to copy the selected range
CopyCell.Copy
'Command to paste the range
PasteCell.Parent.Activate
'command to pasted cells to keep the source formatting
PasteCell.PasteSpecial xlPasteValuesAndNumberFormats
PasteCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

  • Save the program and press  F5   to run it.
  • After appearing the ‘ExcelDemy’ box, select the range $B$4:$C$11.
  • Click OK.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

  • Select any blank cell to paste.
  • Then, Click OK.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

  • Therefore, you will get the paste value by keeping the format intact.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

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


2. Apply VBA PasteSpecial to Select Range and Keep Source Formatting in Excel

In VBA, you may also specify ranges and copy and paste them while keeping the source formatting. Follow the outlined steps below to have done it.

  • Press  Alt  F11  to open Macro.
  • Create a new Module from the Insert
  • Simply, paste the following VBA code for the range B4:C11.
Sub pasteSpecial_KeepFormat()
'select range to copy
Range("B4:C11").Copy
'select cell to paste
'Command to pasteSpecial keeping the Format
Range("E4").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

  • Finally, save the program and press  F5  to run. Consequently, you will see the changes as shown in the image below.

Add Input Box by Using VBA PasteSpecial and Keep Source Formatting in Excel

Read More: VBA Paste Special to Copy Values and Formats in Excel


3. Declare Variable by Applying VBA PasteSpecial and Keep Source Formatting in Excel

By declaring variables and setting variables to different ranges, follow the steps below to PasteSpecial in VBA.

  • First of all, press  Alt  F11  to open VBA Macro.
  • Select a new Module.
  • Then, paste the following VBA Code.
Sub Copy_Paste_Special()
'Declare variables
Dim copy_Rng As Range, Paste_Range As Range
'Command to copy range
Set copy_Rng = Range("B4:C11")
'Command to paste range
Set Paste_Range = Range("E4")
copy_Rng.Copy
'Paste command to pasteSpecial with keeping Format
Paste_Range.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Add Input Box

  • After saving the program, press  F5  to run.
  • Therefore, your copied range will be pasted with keeping the source formatting as before.

Add Input Box


4. Use VBA PasteSpecial and Keep Source Formatting in Different Worksheet in Excel

We’ll talk about a very significant method in this part. Because we’ll go through how to use PasteSpecial in VBA in multiple workbooks. For example, we’ll copy from ‘Sheet4’ and paste it into ‘Sheet5,’ as shown in the steps below.

  • To open VBA Macro, press  Alt  + F11
  • From the Insert tab, choose the Module.
  • Then, paste the following VBA.
Private Sub KeepSourceFormat()
  Application.ScreenUpdating = False
  'Declare variables
  Dim copyRng As Worksheet
  Dim pasteRng As Worksheet
 'Set range destination to the variable
  Set copyRng = Worksheets("Sheet4")
  Set pasteRng = Worksheets("Sheet5")
  'set the destination of the pasted cells
  'For cell E4, Rows.count = 5, Offset = 3
  Set Destination = pasteRng.Cells(Rows.Count, 5).End(xlUp).Offset(3, 0)
  'Command to copy the range
  copyRng.Range("B4:C11").Copy
 'Command to paste the range
   Destination.PasteSpecial Paste:=xlPasteValues
   Destination.PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Add Input Box

  • To run the program, press  F5  after saving the program.
  • Therefore, you will get the pasted value in sheet 5 with keeping the source format.

Add Input Box

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


Download Practice Workbook

Download this workbook to practice.


Conclusion

To summarize, I hope you now know how to use Excel VBA to Paste Special Keep Source Formatting. All of these strategies should be taught to your data and used to it. Examine the practice book and apply what you’ve learned. We’re motivated to keep giving lectures like these because of your important support.

Please do not hesitate to contact us if you have any questions. Please share your thoughts in the comments box below.

Stay with us and continue to learn.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo