CODEWITHSUNDEEP
excelvba
cnutco
cnutco

Reputation: 3

How do I paste starting in column "B"?

This borrowed code starts the paste in column "A", but I need to start in column "B".

If sheet InventoryAvailability column U equals "X", then copy entire row.

Sub MoveRowBasedOnCellValueX()

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("InventoryAvailability").UsedRange.Rows.Count
    J = Worksheets("CountSheet").UsedRange.Rows.Count
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("CountSheet").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("InventoryAvailability").Range("U4:U" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "X" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("CountSheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

I tried changing Range("A" & Rows.Count) to Range("B" & Rows.Count). It runs and nothing happens.

Upvotes: 0

Views: 113

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

If you have the EntireRow you can't offset the pasting range because it would go off the sheet. I've corrected this by changing the Copy region to only be in the used range.

Option Explicit

Sub MoveRowBasedOnCellValueX()

    Dim xRg As Range
    Dim xCell As Range
    Dim ColWide As Long
    Dim I As Long
    Dim J As Long
    Dim K As Long
    
    I = Worksheets("InventoryAvailability").UsedRange.Rows.Count
    J = Worksheets("CountSheet").UsedRange.Rows.Count
    
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("CountSheet").UsedRange) = 0 Then J = 0
    End If
    
    With Worksheets("InventoryAvailability")
        Set xRg = .Range("U4:U" & I)
        ColWide = .UsedRange.Column + .UsedRange.Columns.Count
    
        On Error Resume Next
        
        Application.ScreenUpdating = False
        For K = 1 To xRg.Count
            If CStr(xRg(K).Value) = "X" Then
                .Range("A" & xRg(K).row).Resize(1, ColWide).Copy Destination:=Worksheets("CountSheet").Range("B" & Rows.Count).End(xlUp).Offset(1)
            End If
        Next
        Application.ScreenUpdating = True
    End With
    
End Sub

Something to consider: If this operation is taking longer than a few seconds to accomplish, you should consider pulling all the data into an array, filtering out the stuff you don't need there, then placing the whole data chunk on your destination sheet. it will be much faster... likely less than a second.

Upvotes: 0

Related Questions