CODEWITHSUNDEEP
excelexcel-2010vba
MCT
MCT

Reputation: 27

Getting Runtime error '6': Overflow error

Code was working fine up until a couple of days ago but now getting the subject-line error. Help?

Sub CopyRows()
    Dim bottomL As Integer
    Dim x As Integer
        bottomL = Sheets("Pacer").Range("L" & Rows.Count).End(xlUp).Row: x = 1

    Dim c As Range
    For Each c In Sheets("Pacer").Range("A1:L" & bottomL)
        If (c.Value = "AFSB" Or c.Value = "TEIGIP4T" Or c.Value = "EPP") Then
            Intersect(c.Parent.Columns("A:Q"), c.EntireRow).Copy Worksheets("Portfolio").Range("A" & x + 1)
            x = x + 1
        End If
    Next c

End Sub

Upvotes: 0

Views: 99

Answers (2)

Storax
Storax

Reputation: 12167

Try this 

Option Explicit

Sub CopyRows()
    Dim bottomL As Long
    Dim x As Long
        bottomL = Sheets("Pacer").Range("L" & Rows.CountLarge).End(xlUp).Row: x = 1

    Dim c As Range
    For Each c In Sheets("Pacer").Range("A1:L" & bottomL)
        If (c.Value = "AFSB" Or c.Value = "TEIGIP4T" Or c.Value = "EPP") Then
            Intersect(c.Parent.Columns("A:Q"), c.EntireRow).Copy Worksheets("Portfolio").Range("A" & x + 1)
            x = x + 1
        End If
    Next c

End Sub

Reason explained here

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Variable bottomL As Integer

Will give overflow error the moment it exceeds 32,767 rows. Try declaring it as long

bottomL As Long

Edit: The rule applies to X as well as it is incrementing.

Upvotes: 1

Related Questions