almost there

[THX1138]

after searching the forum for a solution I have managed to get close to what I need. What I am trying to create is a master sheet that has some vlaues in it. This is then used to generate other sheets with further details for each row item.

Below is the code, what I need is for this code to allow there to be text, numerical data, etc. in the cells to the left (referred in the code as b1,b2, and so on).

Right now the code will not exicute unless the cells are blank.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 2 And Target.Row > 1 And Target.Offset(0, 1) = vbNullString _
And Target <> vbNullString Then
Dim ws As Worksheet
Set ws = Me.Parent.Sheets.Add(After:=Me.Parent.Sheets(Me.Parent.Sheets.Count))
With ws
On Error Resume Next
.Name = Target.Offset(0, -1)
If Err.Number <> 0 Then
MsgBox "Unable to create new worksheet.", vbOKOnly + vbCritical, "Error"
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
Set ws = Nothing
Exit Sub
End If
On Error GoTo 0
.Range("A1:A6") = Application.Transpose(Array("PRIORITY", "PART NUMBER", "DESCRIPTION", "TYPE", "REQUESTED BY", "COMMENTS"))
.Range("B1") = Target.Value
.Range("B2") = Target.Offset(0, 1)
.Range("B3") = Target.Offset(0, 2)
.Range("B4") = Target.Offset(0, 3)
.Range("B5") = Target.Offset(0, 4)
.Range("B6") = Target.Offset(0, 5)
End With
Application.EnableEvents = False
Me.Hyperlinks.Add Target.Offset(0, 6), Address:=vbNullString, SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="PART MASTER SHEET"
Application.EnableEvents = True
Set ws = Nothing
Me.Activate
End If
End If
End Sub

Any and all help will be greatly appreciated.

 

[HalfAce]

Hello THX1138,

Right now the code will not exicute unless the cells are blank.

The only cells that are being tested for blank are (1) the Target cell (must not be blank) and (2) the cell in column C of the target row (must be blank).

Is this not what you want?
As it's written, it's working as intended so far.

(And I'm not sure why you would be making B2 of the new sheet equal to a cell in the original
sheet that must be blank in order for the code to execute, but perhaps that's related to your
request, eh?)

 

[THX1138]

1. I have adapted the code from other posts for similar issues at this forum. This code was not made for my particular needs. yes, I know just enough to be dangerous. {=P

2. I do not require that any cells be blank, in fact, it would be nice to not have that limitation at all.

This is the root of my question, how do I get rid of the requirement for blank cells? For my purposes, it would be best if it was required that a given number of cells were filled out before it would generate the new sheet. I am unable to find what part of the code determines this.

 

[HalfAce]

It is this second If statement where your limitations are defined:

If Target.Column = 2 And Target.Row > 1 And Target.Offset(0, 1) = vbNullString _
    And Target <> vbNullString Then

It is stipulating:
If Target.Column = 2 (the changed occurred in column B)
And Target.Row > 1 (change occurred in any row below row 1)
And Target.Offset(0, 1) = vbNullString (cell to the right of the changed cell
- column C in your case - is blank) (This is the one to remove)
And Target <> vbNullString (do not execute if the change was to delete the target cell's value)

To no longer require the cell in column C to be blank you would want to replace that entire
If statement with this:

If Target.Column = 2 And Target.Row > 1 And Target <> vbNullString Then

Although, requiring the cell to the left of the target (column A) could be tested for not being
blank, and that would allow you to do away with all these lines:

On Error Resume Next
    .Name = Target.Offset(0, -1)
    If Err.Number <> 0 Then
      MsgBox "Unable to create new worksheet.", vbOKOnly + vbCritical, "Error"
      Application.DisplayAlerts = False
      .Delete
      Application.DisplayAlerts = True
      Set ws = Nothing
      Exit Sub
    End If
    On Error GoTo 0

which is simply testing for the error of creating a new sheet and trying to name it from a cell
that is blank. (But it'll work fine the way it is too.)

Does this help clear anything up?

[EDIT:] By the way, I like the shot of the baby!

 

[THX1138]

Awesome! That was exactlly what I needed. Thank you very much.

"Put the baby down and slowly step away from the donkey . . ."

 

[HalfAce]

Most welcome.