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

[vpan]

Hi,


Attached is something I am working on.
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 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 columns 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.


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!!

[SamT]

Not Code, only for your edification:

For each Sht in Worksheets
If Sht.Name = Sheets("sheet1").Range("B3") then Exit Sub

Set Found = Sheets("Sheet2").Range("H:H").Find("Yes")
If Found Is Nothing then Exit Sub

Worksheets.Add
Workshhets(woksheets.Count).Name = Sheets(Shet1").Range("B3")

[vpan]

Not Code, only for your edification:

For each Sht in Worksheets
If Sht.Name = Sheets("sheet1").Range("B3") then Exit Sub

Set Found = Sheets("Sheet2").Range("H:H").Find("Yes")
If Found Is Nothing then Exit Sub

Worksheets.Add
Workshhets(woksheets.Count).Name = Sheets(Shet1").Range("B3")

Thank you for the hints on the start!
Ive started with the If code and for else I put the worksheets.add code.
Quick question for, what is the .count in this doing? I tried changing this code to activesheet.name = Sheets("Sheet1").Range("B3") and it wont change the sheet name, keep getting error 9. Mind further explaining?

Worksheets(worksheets.Count).Name = Sheets("Sheet1").Range("B3")

Also, for this part
For Each Sht In Worksheets
If Sht.name = Sheets("Add Request").Range("B3") Then
Exit Sub
when I already have a sheet where the name = B3's value, the code does not stop, it moves onto the Else, Im not sure if its because the cell has numbers (even though they are formatted as text). Any ideas?

[vpan]

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 the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
        Worksheets.Add
        ActiveSheet.name = strSheetName
   Else
    End If
End Sub

Im now really only confused on the YES part, how, if a yes is found in column H, 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

[snb]

Sub M_snb()
   If [not(isref(B3&"!a1"))] Then Sheets.Add(, Sheets(Sheets.Count)).Name = sheet1.Cells(3, 2).Value
   sheets(sheet1.cells(3,2)).range("B1:B5")=sheet1.range("B1:B5").Value
End Sub

[vpan]

thanks for the input! But the issue here is that it specifically references cell a1. 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, then function doesn't need to run, if If at least one yes, then function needs to copy only rows from yes. Any idea how to alter this code to make it a loop?

[snb]

Please do not quote.

[vpan]

Oh...sorry. Removed quote

[snb]

I'd suggest you study the suggestion more in depth.

[vpan]

Sorry I guess I'm just having trouble understanding what is referenced as critiria for that add sheet to occur. Could you possibly explain what's going on? In fairly new to vba, so I'm sorry it's probably a stupid question

[SamT]

Mind further explaining?

Worksheets(worksheets.Count).Name = Sheets("Sheet1").Range("B3")

When you add a sheet, that sheet is the last one in the Sheets Collection. "Sheets.Count" is the index # of the last sheet in the collection.

Also, for this part . . . If Sht.name = Sheets("Add Request").Range("B3") Then

Test for hidden spaces:

Sub SpacesTest()
Dim Sht
MsgBox Replace(Sheets("Add Request").Range("B3"), " ", "***")
For Each Sht In Worksheets
MsgBox Replace(Sht.Name, " ", "***")
Next
End Sub