I've been running this script for a while with not issues, and then today it broke. It's very basic as in I'm just filtering values from one tab and then copying and pasting them onto another tab in the top row. Suddenly though, it will paste the values and then repeat paste the values 19 more times for a total of 20 copy pastes.
Sheets("BSLOG").Select
Range("Q1").Select
Selection.AutoFilter Field:=17, Criteria1:="1"
Range("A1:Q5000").Select
Range("A1:Q5000").Activate
Selection.Copy
Sheets("PENDG TRADES").Select
Range("A1:Q300").Select
ActiveSheet.Paste
Try the next code, please. No need to select, activate anything. In this case, these selections do not bring any benefit, they only consume Excel resources:
Sub testFilterCopy()
Dim shB As Worksheet, shP As Worksheet
Set shB = Sheets("BSLOG")
Set shP = Sheets("PENDG TRADES")
shB.Range("Q1").AutoFilter field:=17, Criteria1:="1"
shB.Range("A1:Q5000").Copy shP.Range("A1")
End Sub
If you want to make the range dynamic (in terms of rows) I can show you how to initially calculate the existing number of rows and set the range to be copied according to it.