PDA

View Full Version : Creating new sheets based on date and other criteria from sheet 1 and 2



vpan
06-27-2016, 06:45 PM
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
06-27-2016, 06:59 PM
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
06-27-2016, 07:17 PM
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
06-27-2016, 08:38 PM
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
06-28-2016, 01:56 AM
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
06-28-2016, 02:11 AM
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
06-28-2016, 02:37 AM
Please do not quote.

vpan
06-28-2016, 02:42 AM
Oh...sorry. Removed quote

snb
06-28-2016, 03:16 AM
I'd suggest you study the suggestion more in depth.

vpan
06-28-2016, 03:36 AM
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
06-28-2016, 06:38 AM
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