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

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

• Press  Alt  +  F11   to start a Macro.
• 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 in the ‘ExcelDemy’ box, select the range $B$4:$C$11.
• Click OK.

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

• You will get the paste value by keeping the format intact.

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

• Press  Alt  +  F11  to open Macro.
• Create a new Module from the Insert
• 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

• Save the program and press  F5  to run.

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

• Press  Alt  +  F11  to open VBA Macro.
• Select a new Module.
• 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.
• Your copied range will be pasted with keeping the source formatting as before.

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

• Oopen VBA Macro, press  Alt  + F11
• From the Insert tab, choose the Module.
• 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

• Run the program, press  F5  after saving the program.
• Get the pasted value in sheet 5 while keeping the source format.

Download Practice Workbook

Download this workbook to practice.

Related Articles

• How to Paste From Clipboard to Excel Using VBA
• How to Copy Visible Cells Only without Header Using VBA
• Excel VBA to Copy Only Values to Destination
• Excel VBA: Copy Cell Value and Paste to Another Cell
• Excel VBA: Copy Row If Cell Value Matches
• Copy and Paste Values to Next Empty Row with Excel VBA
• How to Autofilter and Copy Visible Rows with Excel VBA