Macro to copy and paste rows to another worksheet without it pasting miles down the page

Question

I have been developing a macro, driven by a button within my worksheet, where I can copy rows, and paste them, onto the next available line of another worksheet , when certain criteria are met within a certain column.

The problem I have is that when the macro runs, it does copy and paste onto the other spreadsheet, however, it is pasting into the 8000th row even though there are only ever, on average, 150-200 tasks on the destination sheet.

Also, further to this, it only pastes into the 8000th row and, therefore, overwrites anything already in the row.

I want the macro to run concurrently so that it simply pastes into the next available row each time, and then continues to find the next new row instead of overwriting other rows.

I do have formatting within my log but only in the sense of borders and a couple of data validation dropdown lists.

The code I have developed is below:

Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Demand Log").UsedRange.Rows.Count
J = Worksheets("Change Log").UsedRange.Rows.Count
If J = 1 Then
   If Application.WorksheetFunction.CountA(Worksheets("Change Log").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Demand Log").Range("O5:O" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
    If CStr(xRg(K).Value) = "Change Team" Then
        xRg(K).EntireRow.Copy Destination:=Worksheets("Change Log").Range("A" & J + 0)
        xRg(K).EntireRow.Delete
    End If
Next
Application.ScreenUpdating = True

If anyone can help with the development of this macro it would be much appreciated.

It would also be great if the macro could copy and paste into the destination spreadsheet, but also copy the row to a third spreadsheet before deleting the row from the source sheet.

Many thanks

What would the following achieve J + 0? Nothing. It is J. And what happens when J =0? I am worried you will be attempting to work with a non existent row 0 . — QHarr
I suspect On Error Resume Next this not being closed is hiding errors and i would be careful with using UsedRange to determine rows. — QHarr
And you should delete rows backwards or you may end up deleting incorrectly. — QHarr
"I do have formatting within my log but only in the sense of borders and a couple of data validation dropdown lists." - UsedRange reflects what range has been used in your worksheet, even if you have only used it to put some borders or data validation dropdown lists. — YowE3K
Welcome! Everybody has to start somewhere! As suggested above, the first step is to remove or comment-out the error handling line On Error Resume Next since that prevents you from seeing errors. In the development stage you want to know when something's going wrong. I would also suggest adding a line at the top of each/any module: Option Explicit and then re-compile the VBA (Debug > Compile). This forces you to make sure that variables and properly declared and handled. Also, check out the tour as well as minimal reproducible example. — ashleedawg

YowE3K YowE3K · Accepted Answer · 2017-12-22T09:15:55

Don't use UsedRange to find the last used row in a spreadsheet, especially if you have formatted cells that you don't want to consider as being "used".

A more reliable way would be something like:

J = Worksheets("Change Log").Cells(Worksheets("Change Log").Rows.Count, "A").End(xlUp).Row

(replacing "A" with whatever column you have that will always contain data in a "used" row)

Your code is also calculating J once, and then constantly pasting new information to that Jth row. You need to increment J each time you write a row to the destination.

Perhaps use:

J = J + 1

just before

xRg(K).EntireRow.Copy Destination:=Worksheets("Change Log").Range("A" & J)

(The problems that will occur with the row deletion, which aren't mentioned in your question, can be addressed in another question if you can't work that out. But there are already many questions/answers on Stack Overflow that will address that issue.)

Macro to copy and paste rows to another worksheet without it pasting miles down the page

1 Answers