Excel VBA Copy Range to Another Sheet with Formatting

Very often we need to copy the Excel Range to another sheet with formatting. We can use VBA to automate this task.  Excel VBA Copy Range to Another Sheet with Formatting macro is explained to know how to copy a range to another worksheet using VBA.

How to Copy Range to Another Sheet with Formatting in Excel VBA

Here is the ‘Excel VBA Copy Range to Another Sheet with Formatting‘ macro to copy a range to another sheet with formatting. You can clearly observe that the Excel VBA is copying the given range to another sheet.

Sub Excel_VBA_Copy_Range_to_Another_Sheet_with_Formatting()
Range("A1:E21").Copy Destination:=Sheets("AnotherSheet").Range("A1")
End Sub

The above example macro will copy the given range to another sheet. Macro will copy the Range A1:A21 and paste at Range A1 of Another Sheet. You can edit the sheet name and range to suit your requirement. You can clearly see form the two sheets and notice these points:

• The macro is perfectly copying the range of data to another sheet
• It is also copying the Format of the given range to destination sheet.
• But not the column width.

How to copy the Excel Range including Column widths

It is easy to copy Excel Range to another sheet with formatting and column widths. We have theree solutions, you can implement one of this to suite your process automation.

Method 1: Using PasteSpecial Method to Copy Range and Paste in Another Sheet with Formatting and Column Widths.

Sub Excel_VBA_Copy_Range_to_Another_Sheet_with_Formatfting_ColumnWidth()

Range("A1:E21").Copy Destination:=Sheets("AnotherSheet").Range("A1")
Range("A1:E21").Copy
Sheets("AnotherSheet").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

Method 2: Copying Entire Range of Columns and Paste in Another Sheet.

We copy entire columns of the required range and paste in another sheet. This approach is useful when there is no other data in both the sheets. This will copy range of data including Formatting and Column Widths. Please check the following macro to copy both formatting and column widths.

Sub Excel_VBA_Copy_Range_to_Another_Sheet_with_FormattingAndColumnWidths()

Range("A:E").Copy Destination:=Sheets("AnotherSheet2").Range("a1")
End Sub

The only change in this method is, removing the row numbers from the ranges (A1:E21) and just using the columns A:E.

Method 3: Explicitly specifying the Column Widths

The following macro will copy the range and paste into another sheet. This will also make copy the Formatting and Column widths of the given Range.

Sub Excel_VBA_Copy_Range_to_Another_Sheet_with_FormattingForEachColumn()
Range("A1:E21").Copy Destination:=Sheets("AnotherSheet").Range("a1")
colCntr = 0
For Each col In Range("A1:E21").Columns
Sheets("AnotherSheet").Range("A1").Offset(1, colCntr).ColumnWidth = col.ColumnWidth
colCntr = colCntr + 1
Next
End Sub

Copy Range Values to Another Sheet with out Formatting in Excel VBA

We may need to copy only the values of the given range to another sheet with no formatting. You can copy and paste only values into the another sheet using Excel VBA.

The following macro will copy a range and paste only values in another sheet.

Range("A1:E21").Copy

Sheets("AnotherSheet").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Copy Formats of a Range to Another Sheet using Excel VBA

Alternatively, we can also paste only the formats of the given range into another sheet. We can copy the range use pastespecial method to paste only formats of the source range using Excel VBA.

Here is the macro will copy a range and paste only the formats in another sheet.

Sheets("AnotherSheet").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Copy Formulas of a Range to Another Sheet using Excel VBA

Sometimes, we may need copy the formulas of the given range and paste in to another range. PasteSpecial method allows us to paste only Formulas to the target range and sheet using Excel VBA.

Macro to copy the formulas from source range and paste into another range and sheet.

Sheets("AnotherSheet").Range("A1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

CopyPaste Only the Border of a Range to Another Sheet in Excel VBA

The following macro will help us to copy and paste only the borders of the source range and ignore all other formats. We need this when you wants to maintain same border formats in all our target sheets.

Sheets("AnotherSheet").Range("A1").PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Sheets("AnotherSheet").Range("A1").PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True

Best Practices to follow while Copying Excel Range to another Sheet

Excel VBA macros are very helpfull to copy Excel Range to another sheet. We recommend you to note the below points while automating the copy paste tasks.

• Clear the Target Range: Always clear the target  range (in Another sheet) before copying and pasting the data. There may be some data or formats avaialbe in the target range. This will make sure that you have same source data in the target sheet. And this will clear any pre formats and reduce the file size.
• Specify Source and Destination Sheets: Try to specify both of the source and destination sheets. So that you can run your macro from any sheet. If your source range is alwas from active sheet, then do not specify the source sheet.
• Do not perform any other operations between Copy and Paste. It is noticed many beginners copy the range and do some task and then pasting the values. Excel will turn off the CutCopy mode and no data will be pasted in your destination sheet.