Copy Cells To empty Positions On Different Sheet

[steveb1471]

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

thanks

steve

[vbfbryce]

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
    Selection.Copy
    Sheets("Sheet2").Select
    Range("a65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
End Sub

[koolsid]

@vbfbryce: I wouldn't recommend that method

Reasons

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