Sometimes, you may need to copy a range from one worksheet to another within the same or another workbook.
In this tutorial, I will show you seven examples of copying a range from one worksheet to another, either within the same workbook or another using VBA.
VBA to Copy All Cells to Another Sheet
In VBA, you can use the ‘Range.Copy’ method to copy a source range to another sheet, like copying and pasting using the shortcuts CTRL + C and CTRL + V on the Excel user interface.
In this way, everything in the source range – formatting, data, formulas, comments, validation – is pasted to the destination range.
However, the source column widths are discarded, and the pasted range adjusts to the default column widths of the destination sheet.
Suppose you have the dataset below on Sheet1.
You can use the VBA code below to copy all the cell contents of the range to Sheet2 in the same workbook:
Sub CopyRangeToSheet()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destinationSheet = ThisWorkbook.Sheets("Sheet2")
Set sourceRange = sourceSheet.Range("A1:C12")
sourceRange.Copy destinationSheet.Range("A1")
End SubWhen you execute the subroutine, it copies all cell contents of the source range to Sheet2.
However, it discards the column widths in the original range and adjusts to default column widths on Sheet2:
You will need to manually adjust the column widths of the pasted range to your requirements. Alternatively, You can also see Example #4 where I show how to copy column widths as well by modifying the VPA code.
Explanation of the Code
The VBA code copies all cell contents of the cell range A1:C12 from ‘Sheet1’ to ‘Sheet2’ within the same workbook.
The original column widths are discarded, and the pasted data adopts the default column widths of ‘Sheet2.’ You can change the ‘sourceRange’ and ‘destinationSheet’ variables to suit your requirements.
Also read: VBA to Copy Range to Email Body
VBA to Copy All Cells to Sheet in Another Open Workbook
Suppose you have the dataset below on Sheet2.
You can use the VBA code below to copy all cell contents of the range to Sheet1 in another open workbook:
Sub CopyRangeToAnotherWorkbook()
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destinationWorkbook = Workbooks("Inventory.xlsx")
Set destinationSheet = destinationWorkbook.Sheets("Sheet1")
Set sourceRange = sourceSheet.Range("A1:C12")
sourceRange.Copy destinationSheet.Range("A1")
Application.CutCopyMode = False
End SubWhen you execute the above VBA macro code, it copies all the cell contents of the cell range A1:A12 on Sheet1 of the current workbook to Sheet1 of another open workbook named ‘Inventory.
However, the source column widths are discarded.
You can modify the ‘sourceSheet’ and ‘destinationWorkbook’ variables to fit your requirements.
Explanation of the Code
The VBA code copies the cell range A1:C12 from Sheet1 in the current workbook to Sheet1 of another open workbook named ‘Inventory.xlsx.’ After copying the data, the code clears the clipboard.
Also read: VBA to Copy Sheet to New Workbook
VBA to Copy Cells From a Specific Range to Another Sheet (Same Workbook)
Suppose you have the dataset below on Sheet3.
You can use the VBA code below to copy only part of the dataset, cell range A1:C6, to Sheet4 in the same workbook:
Sub CopyCellsFromSpecificRange()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destinationSheet = ThisWorkbook.Sheets("Sheet2")
Set sourceRange = sourceSheet.Range("A1:C6")
sourceRange.Copy destinationSheet.Range("A1")
End SubWhen you run the above code, it copies only part of the dataset on Sheet3 to Sheet4 of the same workbook:
Explanation of the Code
The VBA code copies the partial cell range A1:C6 from the original range in Sheet1 to Sheet2 within the same workbook.
The data is copied to the range starting at cell A1 on the destination sheet.
Also read: Creating Named Range Using VBA
VBA to Copy Cells to Another Sheet (along with the Column Width)
Assume you have the dataset below on Sheet1 with varying column widths.
You can use the VBA code below to copy all the cell contents of the range to Sheet2 in the same workbook along with the source column widths:
Sub CopyRangewithColumnWidths()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destinationSheet = ThisWorkbook.Sheets("Sheet2")
Set sourceRange = sourceSheet.Range("A1:C12")
sourceRange.Copy
destinationSheet.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Paste
Application.CutCopyMode = False
End SubWhen you execute the subroutine, it copies all the cell contents of the source range to Sheet2, keeping the source column widths.
Explanation of the Code
The VBA code copies the specified range A1:C12 from ‘Sheet1’ to the cell range starting at A1 on ‘Sheet2’ within the same workbook.
The code also copies the column widths and values and clears the clipboard after the operation. You can modify the ‘sourceRange’ and ‘destinationSheet’ variables to fit your requirements.
You can customize the VBA code below to copy the range to a sheet in another open workbook, keeping source column widths:
Sub CopyRangeToAnotherWorkbookKeepColumnWidths()
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destinationWorkbook = Workbooks("Inventory.xlsx")
Set destinationSheet = destinationWorkbook.Sheets("Sheet1")
Set sourceRange = sourceSheet.Range("A1:C12")
sourceRange.Copy
destinationWorkbook.Activate
destinationSheet.Activate
destinationSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Paste
Application.CutCopyMode = False
End SubWhen you execute the subroutine, it copies the source range to the destination worksheet in the destination workbook, keeping the column widths of the source range.
Explanation of the Code
The code copies the range A1:C12 from Sheet1 in the current workbook to the cell range starting at A1 on Sheet1 of the ‘Inventory.xlsx’ workbook.
It preserves column widths during the copy-and-paste process and clears the clipboard afterward.
Also read: VBA to Print Range of Cells to PDF
VBA to Copy Only Cell Values to Another Sheet
In VBA, you can utilize the ‘Range.Copy’ and ‘Range.PasteSpecial’ methods to copy only values (or formula results) of a source range to another sheet.
Suppose you have the below dataset on Sheet3:
Notice the formula and the percentage formatting in the range.
You can use the VBA code below to copy the cell range C2:C12 and paste only the results of the formula into the destination range on Sheet4:
Sub CopyOnlyValues()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet3")
Set destinationSheet = ThisWorkbook.Sheets("Sheet4")
Set sourceRange = sourceSheet.Range("C2:C12")
sourceRange.Copy
destinationSheet.Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End SubWhen you execute the code, only the formula results in the source range are pasted in the destination range:
Notice also that the percentage number formatting has been discarded.
Explanation of the Code
The code copies values from the range C2:C12 in Sheet3 to Sheet4 within the same workbook.
The code utilizes Excel’s copy-and-paste functionality with the ‘PasteSpecial’ method to only paste the values (excluding formulas, formatting, etc.). Finally, the ‘Application.CutCopyMode’ is set to ‘False’ to clear the clipboard and end the copy operation.
Note: Add a variable to the code that points to the other workbook to copy the range to a sheet in another open workbook.
VBA to Copy Only Values and Number Formatting to Another Sheet
Suppose you have the below dataset on Sheet3:
Notice the formula and the percentage number formatting in the range.
You can use the VBA code below to copy the cell range C2:C12 and paste only the results of the formula together with the percentage number formatting into the destination range on Sheet4:
Sub CopyValuesAndNumberFormats()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet3")
Set destinationSheet = ThisWorkbook.Sheets("Sheet4")
Set sourceRange = sourceSheet.Range("C2:C12")
sourceRange.Copy
destinationSheet.Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End SubWhen you execute the subroutine, it pastes only the formula results and number formatting of the source range in the destination range:
Explanation of the Code
The VBA code copies values and number formats from the cell range C2:C12 in Sheet3 to Sheet4 within the same workbook.
The code utilizes Excel’s copy-and-paste functionality with the ‘PasteSpecial’ method, specifying the ‘xlPasteValuesAndNumberFormats’ option to include values and number formats in the paste operation.
Finally, the code sets the ‘Application.CutCopyMode’ to ‘False’ to clear the clipboard and end the copy operation.
Note: To copy the range to a sheet in another open workbook, you can customize the code by adding a variable that points to the other workbook.
Also read: Select Range in Excel Using VBA
VBA to Copy and Transpose Cells in Another Sheet
You can also use VBA to copy a range of cells and then paste the transposed version of this range into another sheet.
Assume you have the dataset below on Sheet5:
You can use the VBA code below to copy the range to Sheet6 in the same workbook and switch rows and columns:
Sub CopyandPasteAsTranspose()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Set sourceSheet = ThisWorkbook.Sheets("Sheet5")
Set destinationSheet = ThisWorkbook.Sheets("Sheet6")
Set sourceRange = sourceSheet.Range("A1:J3")
sourceRange.Copy
destinationSheet.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
End SubWhen you execute the subroutine, it copies the source range, transposes it, and pastes it in the destination worksheet:
Explanation of the Code
The VBA code copies the range A1:J3 from Sheet5 to Sheet6 in the same workbook.
The copy operation includes all aspects of the source range (values, formulas, formatting, etc.), and the paste operation is set to transpose the data, meaning it is pasted with rows switched to columns and vice versa.
After the paste, the code sets ‘Application.CutCopyMode’ to ‘False’ to clear the clipboard and end the copy operation.
In this tutorial, I showed you seven examples of copying a range to another sheet using VBA. I hope you found the tutorial helpful.
Other Excel articles you may also like:
