Deleting All Rows with Bad Cusips for All Sheets

[ismphoto123]

Hello,

I've been fiddling around with multiple codes and macros but none that are working. I have an excel macro that pulls in data, then we run another macro on that excel sheet. When it pulls in data, for whatever reason it is pulling in bad cusips.

So what I need to do is search all sheets AFTER the first sheet (new sheets are generated every time the macro is run so I can't go by sheet name) and delete ALL rows that contain "BASKET". The tricky part is that all of the bad cusips begin with BASKET then have another number. For example, BASKET32323232 and BASKET52655316136.

I just want to add this code to the existing code so it performs AFTER the data is pulled in, OR serves as a separate macro button that can be chosen.

<tbody>
</tbody><colgroup><col></colgroup>

 

[oldbrewer]

what is a cusip, please ?

 

[oldbrewer]

also sheets(3) will always find the third sheet, regardless of the actual names given to the sheets.

 

[ismphoto123]

A cusip is just an identifier, the spreadsheet contains over 10,000 of them. About 9,000 are bad and need to be deleted. All located in Column AV in all spreadsheets after the first one.

Also, there could be six sheets or there could be two.

 

[oldbrewer]

					train1
					bag3
					bus99
					tax144
				the original list as shown below was in cells K1:K6
		we require to delete entire row if K column starts with "basket"
					train1
					basket45
					bag3
					bus99
					basket999
					tax144
			achieved with this very simple macro
			For j = 1 To 6
			If Left(Cells(j, 11), 6) = "basket" Then Rows(j).Select: Selection.Delete Shift:=xlUp
			Next j
			Cells(1, 1).Select
			100 End Sub

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[ismphoto123]

I'm sorry i'm confused how would I use this?

 

[oldbrewer]

the macro looks for any cell in a particular column that has (in this case) "basket" as the first 6 letters and for each one found that row is deleted

 

[ismphoto123]

But the thing that i'm struggling with most is that my original macro populates sheets based on each Fund requested from the macro (could be 2, could be 6 or more). So is there anyway I can edit this so it will affect all sheets except the first one?

 

[FormR]

Hi, you could try this on a copy of your workbook.

Sub M()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Index > 1 Then
        ws.Columns("AV").Replace What:="BASKET*", Replacement:="#N/A", MatchCase:=False
        On Error Resume Next
        ws.Columns("AV:AV").SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
        On Error GoTo 0
    End If
Next ws
Application.ScreenUpdating = True
End Sub