VBA to Copy Rows Based on Cell Value - Issues with Conditional Logic and Performance
I'm not sure how to approach I've been struggling with this for a few days now and could really use some help. I'm working on a personal project and I'm attempting to create a VBA macro that copies rows from one worksheet to another based on a specific cell value in the range. However, I'm running into performance issues when dealing with larger datasets, and the copying doesn't seem to respect the conditional logic I'm implementing. My intention is to copy all rows from 'SourceSheet' to 'TargetSheet' where the value in column B is 'Approved'. The code I've written is as follows: ```vba Sub CopyApprovedRows() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastRow As Long Dim targetRow As Long Dim i As Long Set wsSource = ThisWorkbook.Sheets("SourceSheet") Set wsTarget = ThisWorkbook.Sheets("TargetSheet") lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row targetRow = 1 For i = 1 To lastRow If wsSource.Cells(i, "B").Value = "Approved" Then wsSource.Rows(i).Copy Destination:=wsTarget.Rows(targetRow) targetRow = targetRow + 1 End If Next i End Sub ``` When I run this code, it works for small datasets but significantly slows down when I have over 1,000 rows. Additionally, I’m noticing that some rows that should have been copied aren’t being included in the `TargetSheet`. I suspect it might be due to hidden rows or data types but haven't confirmed this. I've tried adding screen updating and calculation toggles to improve performance: ```vba Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ``` Yet the performance remains sluggish. Can anyone suggest optimizations for this code or alternative methods to execute the task more efficiently? I'm using Excel 2016 and running this on a standard desktop environment. Thanks in advance! I'm using Vba 3.9 in this project. What are your experiences with this? I'd be grateful for any help. For reference, this is a production web app. Is there a better approach?