VBA Copy Cells vs Rows

Using Variables

• By using variables, your code will become more readable (understandable).
• Activating and selecting often leads to mistakes and it severely slows down the operation. Avoiding this is illustrated in this post.
• The following illustrates the Workbook-Worksheet-Range hierarchy.

' The usual approach is e.g.:
'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
'Dim iws As Worksheet: Set iws = wb.Worksheets("Sheet1")
' Note that 'iws' could actually be 'dws', we don't know!

Dim iws As Worksheet: Set iws = ActiveSheet ' unknown worksheet?
Dim wb As Workbook: Set wb = iws.Parent

Dim sws As Worksheet
On Error Resume Next ' check if the worksheet exists
Set sws = wb.Worksheets(CStr(iws.Range("O1").Value)) ' it may be empty!
On Error GoTo 0
If sws Is Nothing Then Exit Sub ' the worksheet doesn't exist

Dim dws As Worksheet: Set dws = wb.Worksheets("Product_Lookup")
Dim dCell As Range
Set dCell = dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(1)

For i = 2 To a
    If CStr(sws.Cells(i, "I").Value) = "False" Then
        sws.Cells(i, "A").Copy dCell
        ' If you only need values then the following is more efficient:
        'dCell.Value = sws.Cells(i, "A").Value
        Set dCell = dCell.Offset(1)
    End If
Next i