Creating new sheets based on date and other criteria from sheet 1 and 2

[vpan16]

Hi
Im attaching screenshots because my document's cell placement is a little to difficult to explain other wise.
Sheet 1: Imgur: The most awesome images on the Internet Sheet 2: Imgur: The most awesome images on the Internet Desired Sheet 4 and onward: Imgur: The most awesome images on the Internet (can ignore columns G-I)
Sheet 1 is the input form. It matches and adds information (B1:b5) for the companies from column A that exist into sheet 2, and those that are not in the sheet 2 into a sheet 3. so the add request macro works perfectly for these three sheets.


What I want is to make an addition into the add request macro.
So what I need is:
Once I hit the add request it does what it already is doing and then, based on the date input in B3 AND in sheet 2 column H if the column says YES, it creates a new tab in the sheet named by the date and posts only the companies where YES was on sheet 2, the data from Sheet 1 B1:b5 again.
If, a sheet with the date inputted already exists, then a new sheet is not needed and the information can go straight into the existing sheet.
Sheet 4 shows the end result I am looking for. You can ignore columni,s G-I, I can code those manually myself, its just the checking for existing sheet and then copying appropriate info into new sheet/existing sheet based on date and YES that I do not know how to do. The names of the sheets and dates are text values by the order of MMDDYY or in this example 062616
So for example, theoretically if I used this workbook and inputted information 30 days straight, then there would be 33 sheets in the document. If I add multiple people in one day, they all go in one day's sheet. Not multiple sheets for the same day.
I hope this makes sense. Any help would be appreciated as I dont know where to even start with the looping for creating the sheets.
Here is the code for the add request macro:

Sub CopyData1()
Dim r As Range
Dim tgt As Range
Dim data As Range
Dim cell As Range
Dim c As Range
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Set WS1 = Worksheets("Add Request")
Set WS2 = Worksheets("Sorted by Company")
Set WS3 = Worksheets("Wishlist")
Set r = WS1.Columns(1).Find("Company")(1)
Set r = WS1.Range(r, WS1.Cells(WS1.Rows.Count, 1).End(xlUp))
Set data = WS2.Columns(2)
For Each cell In r
Sheets("Sorted by Company").Select
    Set c = data.Find(cell)
    If c Is Nothing Then
        Set tgt = WS3.Columns(2)
        Set tgt = tgt.Find(cell.Value)
        If tgt Is Nothing Then
            Set tgt = WS3.Cells(WS3.Rows.Count, 3).End(xlUp).Offset(1, -1)
            tgt.Value = cell.Value
        End If
        tgt.Offset(1).EntireRow.Insert
        tgt.Offset(1, 1).Resize(1, 5).Value = Application.Transpose(WS1.Range("B1:B5"))
   Else
       c.Offset(1).EntireRow.Insert
       c.Offset(1, 1).Resize(1, 5).Value = Application.Transpose(WS1.Range("B1:B5"))
    End If
    Sheets("Wishlist").Select
Next
Sheets("Add Request").Select
End Sub

 

[vpan16]

So I figured out how to create a new sheet based on the cell value

Sub WorksheetChange()    Dim strSheetName As String, wks As Worksheet, bln As Boolean 
    strSheetName = Worksheets("Add Request").Range("B3") 
    On Error Resume Next 
    Set wks = ActiveWorkbook.Worksheets(strSheetName) 
    On Error Resume Next 
    If Not wks Is Nothing Then 
        bln = True 
    Else 
        bln = False 
        Err.Clear 
    End If 
          
     'If the worksheet name does not already exist, name the active sheet as selected cell
    If bln = False Then 
        Worksheets.Add 
        ActiveSheet.name = strSheetName 
    Else 
    End If 
End Sub

</pre>
Im now really only confused on the YES part, how, if a yes is found in column H (only for the companies listed in sheet 1), to map it back to the company name in column B and subsequently use that to map back the info in Sheet one B1:B5. Then in the new sheet created, how to paste that info Company name then B1:B5 across together in a row starting at A2

 

[Trebor76]

For reference this has been cross-posted here

 

[vpan16]

The Yes are on column h sheet 2 and it's not just checking for a single cell but it requires a loop to check all the rows with the companies from sheet1. If no yes occur, then function doesn't need to run, if If at least one yes, then function needs to copy only data where company is in the row from yes. Any ideas on how to create the loop?