Copy Range to Another Sheet with Excel VBA

Goal: Copy a range of data from one sheet to another.

Code Breakdown:

1. Define objects: You’ll define the source and destination sheets, as well as the range to be copied.
2. Copy the range: Use the Copy method to copy the data.
3. Paste the range: After copying, use the PasteSpecial method to paste the data in the desired location.

Detailed VBA Code:

Sub CopyRangeToAnotherSheet()
    ' Declare variables
    Dim SourceSheet As Worksheet ' Source worksheet
    Dim DestinationSheet As Worksheet ' Destination worksheet
    Dim SourceRange As Range ' Range of cells to copy
    Dim DestinationRange As Range ' Range of cells to past
    ' Set references to the source and destination sheets
    Set SourceSheet = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your source sheet name
    Set DestinationSheet = ThisWorkbook.Sheets("Sheet2") ' Replace "Sheet2" with your destination sheet name   
    ' Define the range to copy (e.g., A1:C10 from the source sheet)
    Set SourceRange = SourceSheet.Range("A1:C10")   
    ' Define the first cell of the destination range (e.g., A1 on the destination sheet)
    Set DestinationRange = DestinationSheet.Range("A1")  
    ' Copy the range from the source sheet
    SourceRange.Copy  
    ' Paste the copied range into the destination sheet at the defined location
    DestinationRange.PasteSpecial Paste:=xlPasteAll ' You can also use xlPasteValues, xlPasteFormats, etc.  
    ' Turn off the copy mode (remove the "marching ants" around the copied range)
    Application.CutCopyMode = False
    ' Display a confirmation message
    MsgBox "Data has been copied successfully!", vbInformation
End Sub

Explanation of the Code:

1. Variable Declarations:
   • SourceSheet: Represents the worksheet containing the data to be copied.
   • DestinationSheet: Represents the worksheet where you want to paste the data.
   • SourceRange: Represents the range of cells to be copied.
   • DestinationRange: Represents the cell in the destination sheet where the data will be pasted.
2. Setting Sheet References:
   • Set SourceSheet = ThisWorkbook.Sheets(« Sheet1 »): Specifies the source sheet by name (modify this based on your needs).
   • Set DestinationSheet = ThisWorkbook.Sheets(« Sheet2 »): Specifies the destination sheet by name.
3. Defining the Ranges:
   • Set SourceRange = SourceSheet.Range(« A1:C10 »): Defines the range to copy (in this example, from A1 to C10).
   • Set DestinationRange = DestinationSheet.Range(« A1 »): Defines the starting cell in the destination sheet where the copied range will be pasted.
4. Copying the Range:
   • SourceRange.Copy: This command copies the specified range.
5. Pasting the Range:
   • DestinationRange.PasteSpecial Paste:=xlPasteAll: This pastes the copied range into the destination sheet. The xlPasteAll option pastes everything (values, formats, formulas, etc.). You can change this to xlPasteValues if you only want to paste the values, for example.
6. Turning Off Copy Mode:
   • Application.CutCopyMode = False: This clears the « marching ants » around the copied range after the paste operation is completed.
7. Confirmation Message:
   • MsgBox « Data has been copied successfully! », vbInformation: Displays a message box to confirm that the data has been copied successfully.

Customizing the Code:

• Source Range: You can modify the range to be copied (e.g., A1:C10), or make it dynamic according to your needs.
• Destination Range: You can change where you want to paste the data (e.g., cell A1 of the destination sheet).