0
votes

I am attempting to copy column B from sheet called "Activity Overview" (Only filled cells) and paste in a sheet called "V&VFile" in column E from row 11 onwards, however when i run the macro the cells arent copied and seems to paste blank cells.

The source of the data uses excel formula index match, is this why as it can copy the data as its found from an excel formula?

Sub VVfileFILL()

Dim Lastrow As Double

Lastrow = Worksheets("Activity Overview").Cells(Rows.Count, "B").End(xlUp).Row

ThisWorkbook.Worksheets("Activity Overview").Range("B" & Lastrow).Copy

ThisWorkbook.Sheets("V&V").Range("E" & Rows.Count).End(xlUp).Offset(10, 0).PasteSpecial xlPasteValues

End Sub
1
Your copy line is copying a single cell "B" & Lastrow.SJR
How would i change thisLawrence Forster
Well what's the range you want to copy, does it contain formulae and are there blanks you want to miss out? If so, are the blanks also the result of a formula? A screenshot would help.SJR
Yes it contains formula Index Match to find the values. Its in coloumn B from row 2 all the way to row 62. But if this range changes i want them to be copied as wellLawrence Forster
Are the results of the formula either blank or a number OR blank or text?SJR

1 Answers

1
votes

Don't think one can avoid a loop in this situation.

Sub VVfileFILL()

Dim Lastrow As Long, r As Long, n As Long

n = Worksheets("V&V").Range("E" & Rows.Count).End(xlUp).Offset(10, 0).Row

With Worksheets("Activity Overview")
    Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
    For r = 2 To Lastrow
        If Len(.Cells(r, "B")) > 0 Then 'check not blank
            If WorksheetFunction.CountIf(Worksheets("V&V").Range("E1:E" & n), .Cells(r, "B")) = 0 Then 'check not already in E
                Worksheets("V&V").Cells(n, "E").Value = .Cells(r, "B").Value 'transfer value
                n = n + 1 'add 1 to destination row
            End If
        End If
    Next r
End With

End Sub