For each to copy a sheet and rename based off adjacent cell

[crburke92]

If I have a list in column B & C, Is there a way to copy a premade sheet based on what is in column B, and rename it what is in column C? I have current modules made with a button for each that have

Sub HVCircuitBreaker()
Dim WsName As String: WsName = Application.InputBox("Enter New SheetName")
Dim CPY As Integer: IDX = Sheets("HVCIRCUITBREAKER_TEMP").Index

Sheets("HVCIRCUITBREAKER_TEMP").Visible = True
    Sheets("HVCIRCUITBREAKER_TEMP").Copy After:=Sheets(IDX)
        Sheets("HVCIRCUITBREAKER_TEMP").Visible = False
            ActiveSheet.Name = WsName

End Sub

I wrote the following:

Sub NexMCC1_Click()
Dim rng As Range
Dim Nex As Range
Dim i As Long


If Range("C3").Value = True Then
    For Each rng In Range("Nex")
        For i = 4 To 20
            If Range("B" & i) = "" Then GoTo SheDone
                If Range("B" & i) = "BKR" Then
                    Run "HVCircuitBreaker"
                ElseIf Range("B" & i) = "TRN" Then
                    Run "XFMR"
                ElseIf Range("B" & i) = "PT" Then
                    Run "PotentialTrans"
            End If
            Sheets("SiteList").Activate
        Next
    Next
Else: GoTo DoNothing
End If

    
SheDone: MsgBox ("Export Complete")
DoNothing:
End Sub

Running with the application box, the proper template sheets copy and rename as what I type in. I'm hoping to remove the application box and add something like...

If Range("C3").Value = True Then
    For Each rng In Range("Nex")
        For i = 4 To 20
            If Range("B" & i) = "" Then GoTo SheDone
                If Range("B" & i) = "BKR" Then
                    Run "HVCircuitBreaker"
                        Activesheet.Name = Range("C" & i)

Any help getting this to work would be awesome!

 

[crburke92]

Found a solution:

Sub ExportAllSheets()

Dim DB As Workbook
Dim EL As Worksheet
Dim SN As Range
Dim Tp As Range
Dim List As Range
Dim Name As Range
Dim x As String
Dim i As Long

Set DB = Workbooks("ClientDatabase")
Set EL = DB.Sheets("ExportList")
Set Tp = EL.Range("A2:A100")
Set List = EL.Range("B2:B100")
Set Name = EL.Range("C2:C100")

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.AllowMultiSelect = False
.Title = "Please select the file."

If .Show = True Then
    fileName = Dir(.SelectedItems(1))
End If
End With


ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open (fileName)


Workbooks("ClientDatabase").Sheets("ExportList").Activate

For Each SN In List
    For i = 2 To 100
        If EL.Range("A" & i) = "" Then GoTo SheDone
            If Workbooks("ClientDatabase").Sheets("ExportList").Range("A" & i) = "BKR" Then
                Workbooks("ClientDataBase").Activate
                Sheets("HVCIRCUITBREAKER_TEMP").Visible = True
                    Sheets("HVCIRCUITBREAKER_TEMP").Copy After:=Sheets("ExportList")
                        ActiveSheet.Name = Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Move After:=Workbooks(fileName).Sheets("Master")
                        ActiveSheet.Range("G14") = Workbooks("ClientDatabase").Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Range("G16") = Workbooks("ClientDatabase").Sheets("ExportList").Range("B" & i)
                        ActiveSheet.Range("G14:M14").Merge
                        ActiveSheet.Range("G16:M16").Merge
                            Workbooks("ClientDatabase").Sheets("HVCIRCUITBREAKER_TEMP").Visible = False
                        
            ElseIf Workbooks("ClientDatabase").Sheets("ExportList").Range("A" & i) = "TRN" Then
            Workbooks("ClientDataBase").Activate
                Sheets("TWOWND_TEMP").Visible = True
                    Sheets("TWOWND_TEMP").Copy After:=Sheets("ExportList")
                        ActiveSheet.Name = Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Move After:=Workbooks(fileName).Sheets("Master")
                        ActiveSheet.Range("F14") = Workbooks("ClientDatabase").Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Range("F17") = Workbooks("ClientDatabase").Sheets("ExportList").Range("B" & i)
                            Workbooks("ClientDatabase").Sheets("TWOWND_TEMP").Visible = False
                        
            ElseIf Workbooks("ClientDatabase").Sheets("ExportList").Range("A" & i) = "RLY" Then
                Workbooks("ClientDataBase").Activate
                Sheets("THREEPHASERELAY_TEMP").Visible = True
                    Sheets("THREEPHASERELAY_TEMP").Copy After:=Sheets("ExportList")
                        ActiveSheet.Name = Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Move After:=Workbooks(fileName).Sheets("Master")
                        ActiveSheet.Range("F13") = Workbooks("ClientDatabase").Sheets("ExportList").Range("C" & i)
                        ActiveSheet.Range("F16") = Workbooks("ClientDatabase").Sheets("ExportList").Range("B" & i)
                            Workbooks("ClientDatabase").Sheets("THREEPHASERELAY_TEMP").Visible = False
            End If

    Next
Next

ScreenUpdating = True
Application.DisplayAlerts = True

SheDone:
        MsgBox ("Export Complete")

Workbooks("ClientDatabase").Sheets("ExportList").Range("A2:C100").ClearContents
End Sub

Probably clunky as I actually have ~45 ElseIf statements, but it did the trick for me.