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
thanks
steve
Re: Copy Cells To empty Positions On Different Sheet
There are better ways, but start with something like this:
Code:
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
Re: Copy Cells To empty Positions On Different Sheet
@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 :wave:
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 :)
Code:
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