VBA to create a copy of a tab and rename it - loop through a list

[gheyman]

I have a list on my tab named: Bid Item List. In column B starting in Row 16 I have list (CLIN) and in column C I have a list of Part Number. I also have a tab named: MasterBidTemplate

What I am trying to do is have code that will go down column C (Part Number) and if the cell is not empty, make a copy of the MasterBidTemplate and rename it after the value in Column B

For example: If C16 has a value in it, I want to make a copy of the MasterBidTemplate and rename it the value in cell B16 (for example 1.00)

I can have up to 50 parts going down column C.

If easier I assume code can be put into the MasterBidTemplate to rename itself on deactivation... based on the value in its cell Z1 (where the above code code populate the copied template with the value from B)

Not sure how to loop through VBA to accomplish this.

Thank you in advance for your help.

 

[John_w]

See if this macro does what you want.

Public Sub Copy_Template_Sheet()

    Dim TemplateSheet As Worksheet
    Dim NewTemplateSheet As Worksheet
    Dim r As Long
    
    Application.ScreenUpdating = False
    
    Set TemplateSheet = ThisWorkbook.Worksheets("MasterBidTemplate")
    
    With ThisWorkbook.Worksheets("Bid Item List")
    
        For r = 16 To 65
        
            If Not IsEmpty(.Cells(r, "C").Value) Then
            
                'Add new sheet by copying template sheet
                
                TemplateSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                Set NewTemplateSheet = ActiveSheet
                
                'Rename new sheet as column B cell and put it in Z1
                
                NewTemplateSheet.Name = .Cells(r, "B").Value                
                NewTemplateSheet.Range("Z1").Value = .Cells(r, "B").Value
            
            End If
            
        Next
        
        .Activate
        
    End With
        
    Application.ScreenUpdating = True
        
    MsgBox "Done"
    
End Sub

If easier I assume code can be put into the MasterBidTemplate to rename itself on deactivation... based on the value in its cell Z1

I don't really understand what you mean by this.