Creating a new sheet with each new line item

**Kcadzow** · 03-15-2024, 03:29 PM

I hadn't been able to open the file and run the one that you sent, but I was able to finally do that. It works! Thank you. I was wondering though, and maybe this is too much, but if it could also change the cell on the template for part number C3 to the new part number - the same one as the tab is named as. Maybe that's too much. I don't know, but is it possible to add that layer?

**Bernie Deitrick** · 03-15-2024, 06:49 PM

Sure - just add the line

.Range("C3").Value = Target.Value

After the line

.Name = Target.Value

**Kcadzow** · 03-22-2024, 08:53 AM

Thank you so much for your help! But now, I have one more additional question...
Is there a way to add the safety critical note after the fact. For example, If someone adds the line on the table of contents page, but then doesn't put the yes or no in the safety critical column right at the beginning, but then goes back to that later and puts "yes" for safety critical, is it possible to have the note show up on the tab that had been created already? One of my work colleagues is asking the question as I was super happy with it. If it is not possible, then that will just have to be a manual entry. Just figured I'd ask.

Again, thank you so much for the help you've given so far!

**Bernie Deitrick** · 03-22-2024, 11:26 AM

Try it like this.....

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 And Target.Column <> 3 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Cells(Target.Row, 1).Value = "" Then Exit Sub
    
    On Error GoTo NoSheet
    If Target.Column = 3 Then
        Worksheets(Cells(Target.Row, 1).Value & "s Template - Blank").Copy before:=Worksheets(5)
        Worksheets(5).Name = Target.Value
        If UCase(Cells(Target.Row, 2).Value) = "YES" Then
            CriticalSafety Worksheets(5)
        End If
    End If
    
    If Target.Column = 2 And Cells(Target.Row, 3).Value <> "" Then
        If UCase(Target.Value) = "YES" Then
            CriticalSafety Worksheets(Cells(Target.Row, 3).Value)
        End If
    End If
NoSheet:
    
End Sub
Private Sub CriticalSafety(shtS As Worksheet)
    With shtS.Range("H1:S1")
        .Merge
        .EntireRow.RowHeight = 36
        .Font.Size = 36
        .Interior.ColorIndex = 3
        .Value = "***THIS ITEM HAS BEEN IDENTIFIED AS SAFTEY CRITICAL***"
    End With
End Sub

**Kcadzow** · 03-22-2024, 12:59 PM

It works great as originally requested, but it won't add the safety critical after the fact. If you skip that column, create the new sheet, then go back and choose "Yes" to safety critical, it won't add the safety critical statement. It only works if you choose it before creating the sheet.

Creating a new sheet with each new line item

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Creating a new sheet with each new line item

Re: Creating a new sheet with each new line item

Re: Creating a new sheet with each new line item

Re: Creating a new sheet with each new line item

Re: Creating a new sheet with each new line item

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Vba to match item description and copy/paste item code to other sheet

VBA code to copy line items from form - overriding previous line item

[SOLVED] Formula needed - line item of unique item matching criteria

Flagging an inventory item and moving it to next sheet, to pick the item from storage.

Creating a log sheet macro to copy a generated text string to the next line on a log page

creating a macro to use cell content as subject line in sending email - protected sheet

Combining multiple line items into one line item based on column.

Bookmarks

Bookmarks

Posting Permissions