However, there are situations when we want to duplicate more than simply text. We can copy all of Excel’s capabilities of them, 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 source formatting in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Easy Examples to Apply VBA PasteSpecial and Keep Source Formatting in Excel
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.
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.
- 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
- Save the program and press F5 to run it.
- After appearing the ‘ExcelDemy’ box, select the range $B$4:$C$11.
- Click OK.
- Select any blank cell to paste.
- Then, Click OK.
- Therefore, you will get the paste value by keeping the format intact.
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
- Finally, save the program and press F5 to run. Consequently, you will see the changes as shown in the image below.
- VBA Code to Compare Two Excel Sheets and Copy Differences
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Excel VBA: Copy Range to Another Workbook
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
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
- After saving the program, press F5 to run.
- Therefore, your copied range will be pasted with keeping the source formatting as before.
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
- 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.
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.
Your questions will be answered as soon as possible by the Exceldemy staff.
Stay with us and continue to learn.
- How to Paste From Clipboard to Excel Using VBA
- Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- Autofilter and Copy Visible Rows with Excel VBA
- How to Copy Unique Values to Another Worksheet in Excel (5 Methods)
- Copy Merged and Filtered Cells in Excel (4 Methods)
- How to Copy and Paste Thousands of Rows in Excel (3 Ways)