CODEWITHSUNDEEP
excelvba
NopeCantCode
NopeCantCode

Reputation: 17

VBA Copy Cells vs Rows

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`

Upvotes: 1

Views: 78

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 1

Related Questions