CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

Entire row not being selected when condition is met in VBA

I have a bit of code that is supposed to read every cell in column A one by one and copy and paste the entire row on another worksheet until a cell contains a particular text. Please see code below:

Public Sub CiscoPrimeReport()
Dim rowCounter As Long
Dim colCounter As Long
rowCounter = 9
colCounter = 1

MsgBox ("Please do the following before pressing the OK BUtton on this    Popup Window!!:" & vbNewLine & vbNewLine & _
    "1. Open the CISCO PRIME AP Report you want to use" & vbNewLine & _
    "2. Select all the data (Ctrl + A)" & vbNewLine & _
    "3. Copy ALL the content (Ctrl + C)" & vbNewLine & _
    "4. NOW YOU CAN PRESS THE OK BUTTON!")


Application.DisplayAlerts = False
Call createCiscoSheet


    With ThisWorkbook.Sheets("Cisco Raw")
        .Range("A1").PasteSpecial (xlPasteValues)

        Do While rowCounter < 2200
            If Cells(rowCounter, colCounter).Value <> "AP Statistics Summary" Then
               ThisWorkbook.ActiveSheet.Range("rowCounter:colCounter").EntireRow.Copy

                With ThisWorkbook.Sheets("Throughput Per AP")
                    .Range("A2").PasteSpecial (xlPasteValues)
                End With
            End If
            rowCounter = rowCounter + 1
        Loop

    End With

End Sub

The code runs fine until i get to this row:

ThisWorkbook.ActiveSheet.Range("rowCounter:colCounter").EntireRow.Copy

I then get an Run-Time error 1004: application defined or object defined error.

I dont know if using the Do While loop is the issue and I should use a For loop instead or if I am missing something within the code.

Upvotes: 0

Views: 46

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

The vba variables need to be outside the quotes and concatenated:

Range(rowCounter & ":" & colCounter)

But I do not think that is what you want as it will copy every row from row 1(colCounter) to whatever rowCounter currently is.

I think you want:

ThisWorkbook.ActiveSheet.Rows(rowCounter).Copy

Also if you only want values then skip the clipboard altogether and directly assign the values:

        If Cells(rowCounter, colCounter).Value <> "AP Statistics Summary" Then
             ThisWorkbook.Sheets("Throughput Per AP").Rows(2).Value = ThisWorkbook.ActiveSheet.Rows(rowCounter).Value                
        End If
        rowCounter = rowCounter + 1

Upvotes: 4

Related Questions