Issue with Copy - Paste from one sheet to another for second sheet

Question

I have this code that copies the data from two sheets to one in another workbook. The issue is that it does not copy correctly from the second sheet. It copies only rows 12 and 13 while the range should be row 13 to last row. I am assuming it has to do something with the activecell but have trouble finding it.

Thanks.

I have this code in another file where it copies 20 sheets to one and it works all fine. The only difference is that they are all in the same workbook.

Datasheet

Sub Copy()

For i = 2 To 3

wbk1.Worksheets(i).Activate
LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

Range("AV13:CJ" & LastRow).Select

Selection.Copy
wbk.Sheets("Data").Activate

LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Select

Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, -3).Select
Selection.PasteSpecial xlPasteValues


Next i

First and foremost, avoid activate and select... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Cyril, Jul 16 '19 at 13:15
Have you dimensioned any of your variables and are you using `Option Explicit` at the top of yoru module? You determine last row by selecting... `LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row` should be what you want, with `Row` instead of `Select`, however you DID NOT qualify all of your ranges... `wbk.Sheets("Data").Rows.Count` qualifies what range you are counting, inside of the cells — Cyril, Jul 16 '19 at 13:17

score 0 · Answer 1 · answered Jul 16 '19 at 13:25

Try this: Use F8 to run this Code, that way you will know what is happening at each step. And you will know if t goes wrong.

Sub Copy()

For i = 2 To 3

With wbk1.Worksheets(i)

lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row

.Range("AV13:CJ" & lastrow).Copy

End With

With wbk.Sheets("Data")

.Cells(.Rows.Count, "D").End(xlUp).Select
.Activate

End With


Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, -3).Select
Selection.PasteSpecial xlPasteValues


Next i

End Sub

And my advise is NOT to use Activate and select. Try and configure exact references where you want to paste the Data. In the code I think there was problem in calculating last row. It should work now.

Thanks for the advice, I will try to avoid select as much as possible, I have seen issues with it but it is kind of difficult to avoid it due to its simplicity. Regarding the code, it works the same way as the one from the other answer and the one I used. it copies only rows 12 and 13 from the second sheet. I am staring to think that there maybe the worksheet is corrupted. — Kristian Tonev, Jul 17 '19 at 06:24

Horst · Accepted Answer · 2019-07-17T07:40:40.227

0

I don't know what your sheet Data looks like and I have not tested this but it might work if you defined wbk1 and wbk.

Sub Copy()
Dim wksSource As Worksheet
Dim wksData As Worksheet
Dim LastRowSource As Long, LastRowData As Long
    Set wksData = wbk.Worksheets("Data")
    For i = 2 To 3
        Set wksSource = wbk1.Worksheets(i)
        With wksSource
            lngRowsAV = .Cells(.Rows.Count, "AV").End(xlUp).Row
            .Range("AV12:AV" & lngRowsAV).AutoFilter Field:=48, Criteria1:="=Yes"
            LastRowSource = .Cells(.Rows.Count, "D").End(xlUp).Row
            LastRowData = wksData.Cells(wksData.Rows.Count, "D").End(xlUp).Row
            .Range("AV13:CJ" & LastRowSource).Copy
            wksData.Range("A" & LastRowData + 1).PasteSpecial xlPasteValues
        End With
    Next i
    Application.CutCopyMode = False
End Sub

edited Jul 17 '19 at 07:40

answered Jul 16 '19 at 13:42

Horst

208
1
8

The code works the same way as the one from the other answer and the one I used. It is all good for the first sheet but then it copies rows 12 and 13 from the second. Very strange and I am thinking that maybe there is an issue with the worksheet. – Kristian Tonev Jul 17 '19 at 06:20
Hmm, OK, could you add a screenshot of one of the sheets in wkb1? Preferrably one that shows the cells that are meant to be copied. – Horst Jul 17 '19 at 06:23
I have added a screenshot. The other worksheets are the same format just populated with different data. So everything from row AV13 till CJ end needs to be copied. – Kristian Tonev Jul 17 '19 at 06:42
Is there a special reason why you determine the number of rows to be copied in dependence of column D? – Horst Jul 17 '19 at 06:50
I was just about to say that. There is a reason for one of the sheets but I missed to see that the rest do not need to follow that rule. So simply adding a filter to the sheet solved the problem. Thanks. Btw can I add this filter Selection.AutoFilter Field:=48, Criteria1:="=Yes" somewhere in your code but without the selection? – Kristian Tonev Jul 17 '19 at 07:03
That will probably work with your code. Since mine does not use selections: What did you select? :-) The whole table? – Horst Jul 17 '19 at 07:10
I manually added a filter for certain rows but I would like to put it in the code if it is possible. Now I have switched the row count to simply count all rows and then add a filter to the rows that I need to copy. Could it be incorporated somehow here? wksSource.Range("AV13:CJ" & LastRowSource).Copy – Kristian Tonev Jul 17 '19 at 07:19
Hmm, I edited my answer. I'm not sure if this works. :-) There's still that columnD-dependency. But you'll know how to handle that. ;-) – Horst Jul 17 '19 at 07:42

Issue with Copy - Paste from one sheet to another for second sheet

2 Answers2