My Blog

Copying and Pasting Range

1

In continuation from here

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 one worksheet to another worksheet in the same workbook
  3. Copy range from one 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 to B10 in Sheet1 to range D1 to 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 to 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 to 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
  1. Overmind
    Overmind06-23-2013

    Thanks, really helped me out a lot there :)