VBA to create new sheets with names from If Statement

[darin100K]

I would really appreciate it if someone could help me with some VBA code for a project. I'm looking to search all "Yes" entries in column N and then create a new worksheet named with the cell contents from column C of the same row. I've tried using "For Each", but must be missing something. Thanks so much.

 

[anthonya2369]

Dim iCell As Range

For Each iCell In Range("n1:n65000")
If iCell.Value = "Yes" Then
    Sheets.Add
    ActiveSheet.Name = iCell.Offset(0, -11).Value
End If
Next iCell

[/code]

 

[darin100K]

Yes, this worked. Thanks so much. One more thing...Can you help me figure out why this won't work? iCell.Offset(0,-12).Value and TextBox1.Value are both dates.

For Each iCell In Range("n1:n65000")
If iCell.Value = "Yes" And iCell.Offset(0, -12).Value < TextBox1.Value Then
iCell.Value = "Yes" & " " & Text
wb.Sheets.Add
wb.ActiveSheet.Name = iCell.Offset(0, -11).Value
End If
Next iCell

Thanks again.

 

[Zack Barresse]

Column N is column number 12. If you offset it by negative 12, that puts it one column left of column A, which will error out on you, as it's not possible.

 

[darin100K]

i might be crazy, but im counting column n as 14 and therefore still pondering the trouble with my code. Can someone help me either with my counting skills or lack of VBA skills?

 

[Zack Barresse]

Doh!

If you change this line ..

If iCell.Value = "Yes" And iCell.Offset(0, -12).Value < TextBox1.Value Then

to this ..

If iCell.Value = "Yes" And Cells(iCell.Row, "C").Value < TextBox1.Value Then

Does the same error occur? Btw, column C should be an offset of 11 from column N. Also, I think using autofilter may be a little faster than looping through the entire column, or at least setting a dynamic last row.

 

[darin100K]

I really appreciate your patience with me. As you can tell, I am still quite the newbie. Here is what I have and it's still not working. I'm guessing it has to do with the first line. Also, I'm sure you're quite right about autofilter being faster, but like I said, I'm very new. Thanks again for your help.

For Each iCell In Range("n1:n65000")
If iCell.Value = "Yes" And Cells(iCell.Row, "C").Value < TextBox1.Value Then
iCell.Value = "Yes" & " " & Text
wb.Sheets.Add
wb.ActiveSheet.Name = iCell.Offset(0, -11).Value
End If
Next iCell

 

[Zack Barresse]

Can you explain 1) the logic you are trying to accomplish (in your words) and 2) what exactly the error is andwhat it says?

 

[darin100K]

This is just a snippet of a larger code. Column B contains dates. Column C contains names. Column N contains Yes/No. I have a userform where a date is entered. If the date entered is greater than the date in column b, and column n is "yes", I want to create a new worksheet in workbook wb with column C as the name.

It isn't giving me an error per se, but just opening up workbook wb and not creating the new worksheets as desired.

Any ideas? Thank You.

 

[Zack Barresse]

I have an idea that it's your textbox. If it isn't entered in exactly as excel will recognize it as a date, then you're scre***. I recommend using three combobox's, populating them at Initialize with their respective values. Then you Dim a variable as a Date type and use it something like this ..

myDate = DateSerial(ComboBox1.Value, Combobox2.value, Combobox3.value)

This assumes:
Combobox1 = Your year, 4 numeric digits only
Combobox2 = Your month, 1 or 2 numeric digits only
Combobox3 = Your day, 1 to 31 numeric only

If you want to use a string as month names in your combobox, you can do that as well. I'd take the ListIndex value (remembering it starts at 0 instead of 1) and use the MonthName() method.

I.e. ..
Dim myMonth as string
'...
myMonth = MonthName(Combobox2.listindex + 1, False)

Note: the True/False at the end is for abbreviation or not.

I find this to be a most effective way of getting the user to enter an excel-recognized date with the least amount of overhead. You can of course use DTpicker, although if this file is to be used anywhere else other than the one it's programmed on I recommend against it. It's just more pain than it's worth, IMHO.