I have this code below which is searching for multiple strings, defined with the object "r" and creates a new Sheet for each category. The problem is that my data source keeps on updating in a loop and I keep on rerunning this code, which consequently tries to re-create the same sheets over and over. So I need to add the following.
before creating a new sheet it should check whether the sheet already exists, and if so add the data to the bottom of it, if not create a new sheet.
I would also like to add a function, which will run every time at the end of this code, to delete the duplicates from every sheet separately, defined with the object "r".
Dim f As Range, fa As String, i As Long, r As Range, ws As Worksheet
Dim src As Worksheet, dst As Worksheet
Set src = Sheets("sheet2") 'sheet to be searched, change as required
i = 1
For Each r In Sheets("Sheet3").Range("A1", Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp))
Set f = src.Cells.Find(What:=r.Value, after:=src.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
If Not f Is Nothing Then
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = r
fa = f.Address
If Len(f.Value) < 80 Then
ws.Range("A" & Rows.Count).End(xlUp)(2) = f.Value
i = i + 1
Set f = src.Cells.FindNext(f)
Loop Until fa = f.Address
fa = ""