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

0
votes

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
2
excelvba
First and foremost, avoid activate and select... stackoverflow.com/questions/10714251/… - Cyril
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

2 Answers

0
votes

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
0
votes

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.