Results 1 to 3 of 3
  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012

    Copy Cells To empty Positions On Different Sheet

    Hi Was wondering if sombody could help

    I have a excel problem

    i need to copy a complete row of data into a new sheet but the code needs to move to the next blank row

    I was thinking of using the Selection.End(xlDown).Select command but i have to admin my VB isnt up to scratch to use it

    If i had the row of data in Sheet 1 row 2 and i need the whole row pasted in the next available row in sheet 2

    is there a simple code to do it



  2. #2
    Join Date
    Oct 2008
    Midwest Region, United States

    Re: Copy Cells To empty Positions On Different Sheet

    There are better ways, but start with something like this:

    Sub copy_row()
        ActiveCell.Rows("1:1").EntireRow.Select   'selects entire row of active cell
        ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub

  3. #3
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Mumbai, India

    Re: Copy Cells To empty Positions On Different Sheet

    @vbfbryce: I wouldn't recommend that method


    1) Avoid words like ActiveCell, Selection. Directly perform the action that you want to do.
    2) Avoid words like .Select. They are a major cause of errors and also slow down your code.
    3) Avoid Hard-coded values like "65536". What if the user is using Excel 2007/2010/2011 and has data up till 70k rows?

    @steveb1471: Welcome to the forums

    I wouldn't recommend xlDown as it will give you undesired results if your data in Col A will have blank cells in between. See this code. I have commented it so you shouldn't have any problem understanding it. Still if you do then do not hesitate to ask

    Sub Sample()
        Dim wsI As Worksheet, wsO As Worksheet
        Dim LastRow As Long
        Set wsI = Sheets("Sheet1") '<~~ Input Sheet
        Set wsO = Sheets("Sheet2") '<~~ Output Sheet
        '~~> Get the next available row in Sheet2
        LastRow = wsO.Range("A" & Rows.Count).End(xlUp).Row + 1
        '~~> Copy row 2 from sheet1 to next available row in Sheet2
        wsI.Rows(2).Copy wsO.Rows(LastRow)
    End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.