Copy a worksheet and rename it to the project name in a given cell.

[Jay3]

Hi All,

I have a sheet called "new project requiring board". in column H I have a Yes/No drop down validation list.

If the user selects yes I would like a template / worksheet tab called 'Project Board Template' to be duplicated and renamed to the project name in the corresponding row in col A of "new project requiring board"

My code to trigger this event is on lines 11 -16 of the following script, it would be great if someone could help with this.

Many thanks =D

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As String
  Dim NewValue As String

  ' don't trigger any events if user is making some sort of change to multiple
  ' rows or columns simultaneously
  If Target.Cells.Count > 1 Then Exit Sub
 
  If Target.Column = 1 Then ThisWorkbook.Save
 
  If Not Intersect(Target, Columns("F")) Is Nothing Then
    If Target.Value = "YES" Then
      Call Create_Project_Board(Target.Row)
      ThisWorkbook.Save
    End If
  End If
 
    
 If Not Intersect(Target, Columns("F")) Is Nothing Then
    Select Case Target.Value
      Case "YES"
        NewValue = Target.Value
        Application.EnableEvents = False
        Application.Undo
        OldValue = Target.Value
        Target.Value = NewValue
        Application.EnableEvents = True
        If OldValue = "NO" Then
          ThisWorkbook.Save
            If Target.Value = "YES" Then
              MsgBox "Project Board Already Created"
            End If
        End If
      Case "NO"
        NewValue = Target.Value
        Application.EnableEvents = False
        Application.Undo
        OldValue = Target.Value
        Target.Value = NewValue
        Application.EnableEvents = True
        If OldValue = "YES" Then
          ThisWorkbook.Save
             If Target.Value = "NO" Then
              MsgBox "Should we offer to delete the board here?"
            End If
        End If
    End Select
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Not Intersect(Target, Columns("B")) Is Nothing Then
    If IsEmpty(Target) Then
      ThisWorkbook.Save
    End If
  End If

End Sub

 

[Jay3]

Can you tell me how to change the tab colour?

I need to incorporate this somehow.

With ActiveWorkbook.Sheets(wsName).Tab
      Color = 65280
   End With

Thanks

 

[Fluff]

Have you seen post#20? If so please respond.

 

[Jay3]

@Jay3
You have been here long enough to know that you should not create duplicate threads. Please ensure you do not do it again.
I have merged both threads.

Hi, Sorry haven't used this forum for years. But will keep it mind. How do I close a thread when answered?
Thanks

 

[Jay3]

Ok, change the Change event to call the other sub like

Call Create_Project_Board(Range("A" & Target.Row).Value)

and then use

Sub Create_Project_Board(WsName As String)
'if Yes is selected in column F check if a board has already been created with the corresponding project name. If yes then do nothing if not then create
'To create the board copy the tab called Project Template Board and rename the tab to the corresponding project name in cell A.
'Once done update project title and assigned to fields in c2 and c3 and also the project description in F by copying this information from the New Project
'Requiring Board tab


Dim wsData As Worksheet

Set wsData = ThisWorkbook.Sheets("New Project Requiring Board")

wsData.Copy Before:=Sheets(5)
With ActiveSheet
   .Range("B2").Value = WsName
   .Name = WsName
End With
wsData.Select

End Sub

Hi, Sorry missed this post! Thanks.

 

[Fluff]

To change the tab colour use

With ActiveSheet
   .Range("B2").Value = WsName
   .Name = WsName
   .Tab.Color=65280
End With

How do I close a thread when answered?

We don't close threads here. Just don't start a new thread, when you have already asked the same question on an existing thread.

 

[Jay3]

To change the tab colour use

With ActiveSheet
   .Range("B2").Value = WsName
   .Name = WsName
   .Tab.Color=65280
End With

We don't close threads here. Just don't start a new thread, when you have already asked the same question on an existing thread.

Great thanks

 

[Fluff]

Glad we could help & thanks for the feedback