Excel VBA to Copy Rows to a New Sheet Based on Cell Color - 'Method of Range scenarios' scenarios
Hey everyone, I'm running into an issue that's driving me crazy. I need some guidance on Quick question that's been bugging me - I'm trying to create a VBA script that copies rows from one sheet to another based on the background color of a specific cell in each row... I'm using Excel 2019, and I want to copy all rows where the cell in column B is filled with a specific color (let's say RGB(255, 0, 0) for red). Despite my efforts, I'm working with a 'Method of Range failed' behavior. The code I've written is as follows: ```vba Sub CopyRedRows() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim lastRow As Long Dim destRow As Long Dim i As Long Set wsSource = ThisWorkbook.Sheets("Source") Set wsDest = ThisWorkbook.Sheets("Destination") lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row destRow = 1 For i = 1 To lastRow If wsSource.Cells(i, 2).Interior.Color = RGB(255, 0, 0) Then wsSource.Rows(i).Copy Destination:=wsDest.Rows(destRow) destRow = destRow + 1 End If Next i End Sub ``` I've checked the sheet names and ensured that the 'Destination' sheet is correctly set up. However, the behavior occurs on the line where I'm trying to copy the row. I suspect it might be related to how I'm referencing the rows or the destination range. I've also tried defining the range explicitly using `Set` but still face the same scenario. Is there a specific way to reference the rows correctly or any additional checks I should perform before the copy operation? Any help would be appreciated! I'm working on a CLI tool that needs to handle this. Any ideas what could be causing this? I recently upgraded to Vba stable. I'd love to hear your thoughts on this.