Copy rows to different sheet starting at a specific row.

I am having a little mental block (old age) finding a solution to this problem. I use the following code to copy entire rows from one sheet to another and this is pretty standard.

rng.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Assuming that Sheet2 is blank, this of course copies the rows to Sheet2 starting at Cell A2 and down column A. I would like to modify the code so that I can specify at which row the copy should start. For example, I might want to start the copy at A1 instead of A2, or at A10 instead of A2. Any help would be greatly appreciated.

Re: Copy rows to different sheet starting at a specific row.

Thank you so much for your quick reply. Unfortunately, that won't work because when I'm copying multiple rows, I want each row to be copied below the other, hence the Offset(1,0). With Offset(0,0), the rows just overwrite each other until the last one appears.

Also posted at: http://www.excelforum.com/exce…ecific-row.html?p=3287885

Re: Copy rows to different sheet starting at a specific row.

I was given a solution at http://www.excelforum.com/excel-prog...html?p=3287885. Here is the code:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim RN As Long
    RN = 1
    For x = 1 To 250
        If Cells(x, 3) <> 0 Then
            Cells(x, 1).Resize(, 3).Copy Sheets("Sheet2").Cells(RN, "A")
            RN = RN + 1
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Re: Copy rows to different sheet starting at a specific row.

Hello anthonys213. According to Forum rules, you should not post your question in another person's thread. Please start your own thread and include a copy of your file with some sample data. Provide a detailed explanation of what you would like to see happen. Once you have done this, if you send me a private message with a link to your thread, I will be happy to have a look at it. Cheers. :smile: