none
Copy used range starting a row 2 to another worksheet RRS feed

  • Question

  • Could you tell me what would be the syntax to copy data that starts at row 2 columns A to F, all data down to each columns last row, and copy it to another worksheet, with data landing in row 2 columns A to F?

    Thanks

    Saturday, March 17, 2018 10:07 PM

Answers

  • Try this. You'll have to substitute the actual names of the source and target worksheets.

    Sub CopyData()
        Dim wshSource As Worksheet
        Dim wshTarget As Worksheet
        Dim lngLastRow As Long
        ' Change the names as needed
        Set wshSource = Worksheets("Sheet1")
        Set wshTarget = Worksheets("Sheet2")
        ' Determine the last row of the source sheet
        lngLastRow = wshSource.Range("A:F").Find(What:="*", _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ' Temporarily suspend screen updating
        Application.ScreenUpdating = False
        ' Copy the data
        wshSource.Range("A2:F" & lngLastRow).Copy _
            Destination:=wshTarget.Range("A2")
        ' Clear the clipboard
        Application.CutCopyMode = False
        ' Resume screen updating
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Sunday, March 18, 2018 6:40 PM
    Saturday, March 17, 2018 10:53 PM

All replies

  • Try this. You'll have to substitute the actual names of the source and target worksheets.

    Sub CopyData()
        Dim wshSource As Worksheet
        Dim wshTarget As Worksheet
        Dim lngLastRow As Long
        ' Change the names as needed
        Set wshSource = Worksheets("Sheet1")
        Set wshTarget = Worksheets("Sheet2")
        ' Determine the last row of the source sheet
        lngLastRow = wshSource.Range("A:F").Find(What:="*", _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ' Temporarily suspend screen updating
        Application.ScreenUpdating = False
        ' Copy the data
        wshSource.Range("A2:F" & lngLastRow).Copy _
            Destination:=wshTarget.Range("A2")
        ' Clear the clipboard
        Application.CutCopyMode = False
        ' Resume screen updating
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Sunday, March 18, 2018 6:40 PM
    Saturday, March 17, 2018 10:53 PM
  • Yes that works great! Thanks!
    Sunday, March 18, 2018 6:40 PM