Hopefully someone can put me out of my misery, as I've hit a brick wall with this one! So here's the plan:

Sheet1: Contains all the data, pasted in on a monthly basis

Sheet2 (Mapping Table): Contains the words which I'll be using in my autofilters

In future the names used in the report pasted into Sheet1 could change, so that's why I've tried to build a mapping table of sorts, rather than hard coding the exact words into the macro itself (I did write a macro like that, but I'm handing this over to someone to use in future and would like to 'future-proof' it).

So I've taken a few different approaches and nothing seems to work. The main problem I seem to get is that blank spaces in my mapping table seem to stop the autofilter from functioning correctly and I just end up with no information at all. I've allowed for up to five different options per column, but usually only 1-2 options per column are used. I know the code below is incorrect, as the autofilters will override the one above (so only the last one counts), but I tried this approach because I wanted to stop those blanks being an issue (clearly this doesn't work properly though). Hopefully it'll give an idea as to what I'm aiming for, which is to have up to five words used in the filter in four different columns.

'Sets how many companies we're looking forDim n As Long

For n = 1 To Application.InputBox(prompt:="Enter number of searches", Title:="Searches", Type:=1)

'Finds last row of data on the spreadsheet

Sheets("Sheet1").Select

LastRowWithValueInColumnA = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

Dim wb As Workbook

Dim shF As Worksheet

Set wb = ActiveWorkbook

Set shF = wb.Sheets("Mapping Table")

If Sheets("Sheet1").FilterMode Then

Cells.AutoFilter

End If

'Filtering Data

With wb.Sheets("Sheet1").Range("A1:BB" & LastRowWithValueInColumnA)

If shF.Range("B" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("B" & n).Text

If shF.Range("C" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("C" & n).Text

If shF.Range("D" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("D" & n).Text

If shF.Range("E" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("E" & n).Text

If shF.Range("F" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("F" & n).Text

If shF.Range("G" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("G" & n).Text

If shF.Range("H" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("H" & n).Text

If shF.Range("I" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("I" & n).Text

If shF.Range("J" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("J" & n).Text

If shF.Range("K" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("K" & n).Text

If shF.Range("L" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("L" & n).Text

If shF.Range("M" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("M" & n).Text

If shF.Range("N" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("N" & n).Text

If shF.Range("O" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("O" & n).Text

If shF.Range("P" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("P" & n).Text

If shF.Range("Q" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("Q" & n).Text

If shF.Range("R" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("R" & n).Text

If shF.Range("S" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("S" & n).Text

If shF.Range("T" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("T" & n).Text

If shF.Range("U" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("U" & n).Text

If shF.Range("V" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("V" & n).Text

If shF.Range("W" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("W" & n).Text

If shF.Range("X" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("X" & n).Text

If shF.Range("Y" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("Y" & n).Text

If shF.Range("Z" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("Z" & n).Text

End With

'Copying Data post filtering

wb.Sheets("Sheet1").Range("A1:BB" & LastRowWithValueInColumnA).Offset(0, 0).SpecialCells(xlCellTypeVisible).Copy

Sheets.Add After:=ActiveSheet

Cells.Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

'Name sheet

ActiveSheet.Name = shF.Range("A" & n)

'Clear filter

Sheets("Sheet1").Select

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

'

Next n

Please could someone suggest where I'm going wrong or if there's a better way of approaching this?

I would be most grateful for any help or suggestions

Thanks,

Sam