(Solved) – VBA code not working unless specific sheet is active

  • by
(solved)-–-vba-code-not-working-unless-specific-sheet-is-active

I would like to cut and move rows to another worksheet if certain cells meet criteria. Below code works when the “Trading” worksheet is active; but I got

‘Runtime error 1004’

if I run it when I was on other worksheet.

Sub GetMarketingRows()

Dim TradingStock As Worksheet
Dim r As Long
Dim AllItems As Range

Set TradingStock = Worksheets("Trading")
Set AllItems = TradingStock.Range("A1", Range("A1").End(xlDown))

For r = AllItems.Rows.Count To 2 Step -1

    If (TradingStock.Cells(r, 8) Like "Marketing" Or Cells(r, 7) Like "F-Marketing") Then
    TradingStock.Rows(r).EntireRow.Cut Destination:=Worksheets("Marketing").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    TradingStock.Rows(r).EntireRow.Delete

    End If

Next r

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *