Issue with Copying Data From One Sheet to Another

Question

I developed a script by looping method to copy data from one sheet to another and then refresh the first field. The script works well except for one issue. Within the second sheet it is supposed to find the last non blank cell in column A (i.e. 'x') and past the data from next row. The problem is that it is pasting the data in 'x + 11' row.

I need help identifying why this is occuring.

Note that here i = 11 because the data in row 10 is a header and the required data starts from 11th row.

Here is the macro I have made:

Option Explicit

Sub CopyPaste()
    Dim i As Integer
    Dim x As Long
    Dim y As Long
    Dim c As Range

    i = 11

    Do While Cells(i, 1).Value <> ""
    'ActiveSheet.CopyPast

    x = i
    y = 0
    For Each c In Worksheets("CDS").Range(Cells(x, 1), Cells(x, 11))
        Worksheets("DataBank").Range("a10000").End(xlUp).Offset(x, y) = c

        y = y + 1
        x = 0
    Next c
    Application.CutCopyMode = False

    i = i + 1
    Loop
    With Sheets("CDS")
        Range("A11:K65").ClearContents
    End With
End Sub

for first sheet x should be i to start the range from 11th row but second sheet should be offset with x = 0 to past the data in first non blank cell in column A @TonyDallimore — Vizzi, Oct 10 '14 at 12:42
Just a note on your code. It's much harder to follow when you use one letter variables such as x and y. Also you should name the ranges you are working with. But I do like that you're using `Option Explicit`. Good job on that! — BradyK, Oct 10 '14 at 12:47

Mr. Mascaro · Accepted Answer · 2014-10-10T13:36:50.337

0

It is because you are using an offset that starts at 11 and increases by one for each iteration of the loop.

Change: Worksheets("DataBank").Range("a10000").End(xlUp).Offset(x, y) = c

To: Worksheets("DataBank").Cells(Rows.Count,y+1).End(xlUp).Offset(1, 0) = c

edited Oct 10 '14 at 13:36

answered Oct 10 '14 at 12:45

Mr. Mascaro

2,693
1
11
18

I'm not sure I follow what you're saying. Can you update your question with an example? – Mr. Mascaro Oct 10 '14 at 13:18
thnx for your quick reply its past data from first blank cell in column A now but the order of data is disturbed let say the data range is A11 to K11 and first blank row is 10 in second sheet. now it is doing in this way. A11 in A10 on target sheet, B11 in B11 on target sheet, C11 in C11 on target sheet and so on – Vizzi Oct 10 '14 at 13:21

Issue with Copying Data From One Sheet to Another

1 Answers1