In continuation from here

We already covered here how to create the form, set references and place the required buttons.

There are many times when you want to copy a range from one area to another or from one worksheet to another. I would be covering these 3 scenarios.

  1. Copy range from one area to another in the same worksheet
  2. Copy range from one worksheet to another worksheet in the same workbook
  3. Copy range from one worksheet to another worksheet in a different workbook

To begin with, put a 4th button on your form and name it Copy Range as shown in the picture below.

We will be using this button for copy and pasting the range.


Scenario 1 : Copy range from one area to another in the same worksheet

Let’s say we want to copy a range A1:B10 in Sheet1 to range D1:E10. See this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")

        '~~> Set the destination range
        xlDestRange = xlWorkSheet.Range("D1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Scenario 2 : Copy range from one one worksheet to another worksheet in the same workbook

Now if we want to copy a range A1:B10 from Sheet1 to A1 in Sheet2 then use this code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        '~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook.Sheets("Sheet2")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")
        '~~> Set the destination range
        xlDestRange = xlWsheet2.Range("A1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub
End Class

When you run this, you will notice that the range is now copied successfully.


Scenario 3 : Copy range from one one worksheet to another worksheet in a different workbook

Now let’s say we want to copy a range A1:B10 from Sheet1 in Sample.xlsx to A1 in Sheet1 of Book1.xlsx. See this code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook, xlWorkBook2 As Excel.Workbook
    Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Opens Destination Workbook. Change path and filename as applicable
        xlWorkBook2 = xlApp.Workbooks.Open("C:\Tutorial\Book1.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        '~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook2.Sheets("Sheet1")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")
        '~~> Set the destination range
        xlDestRange = xlWsheet2.Range("A1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class