VBA Copy Cells vs Rows

Question

I don't use VBA much at all. I found some code online that has me moving in the right direction, however, I'm struggling how to copy just the values in column "A" rather than copying all the rows. The bold/italic area is where I believe the problem lies.

`For i = 2 To a
    If Sheets(Range("O1").Value).Cells(i, 9).Value = "False" Then
        ***Sheets(Range("O1").Value).Rows(i).Copy***
        Worksheets("Product_Lookup").Activate
        B = Worksheets("Product_Lookup").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Product_Lookup").Cells(B + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Product_Lookup").Activate`

`Sheets(Range("O1").Value).Range("A" & i).Copy`. However a filter might be more efficient than a loop here. — BigBen, Jan 18 '22 at 14:59
I think this will solve my issue (For now) XD. Thank you for your help! — NopeCantCode, Jan 18 '22 at 15:04

score 1 · Accepted Answer · answered Jan 18 '22 at 15:49

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

Thank you for this insight! I'll look up more on variables! – NopeCantCode Jan 18 '22 at 16:02 — NopeCantCode, Jan 18 '22 at 16:02

VBA Copy Cells vs Rows

1 Answers1

Using Variables