Create a copy of a worksheet and name it based on a list

0
votes

Hi I'm trying to create a copy of a worksheet in a workbook for each entry in a range, then rename the worksheet based on the value of the current cell in that range. It was working before, but now it doesn't name the new sheets. If I make blank worksheets, it will name them, however if I copy the worksheet it won't name the worksheet properly. I am also trying to set the value of C1 on each sheet to the value that is from the range. Below is my code:

    Sub CreateSEMSheets()
    On Error GoTo GetOut


    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Strategic End Market Data").Range("SEMListGenerated")

    For Each MyCell In MyRange
    If MyCell.Value = "" Then GoTo GetOut

        Sheets("StrategicMktPlan").Copy After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "SMP - " & MyCell.Value
        Sheets(Sheets.Count).Range("C1").Value = MyCell.Value
    Next MyCell

GetOut:

End Sub

Please help!!! Thanks in advance.

Edit: I figured out why it's not working - there was a hidden sheet that was the last sheet in the workbook and it was renaming that over and over. Any idea how to prevent this?

2
vbaexcel

2 Answers

2
votes

After Copy() method of Worksheet object the newly created worksheet is the active one:

For Each MyCell In MyRange
    If MyCell.Value = "" Then GoTo GetOut

    Sheets("StrategicMktPlan").Copy After:=Sheets(Sheets.Count)
    With ActiveSheet
        .Name = "SMP - " & MyCell.Value
        .Range("C1").Value = MyCell.Value
     End With
Next MyCell
0
votes

Per your edit, you could use this:

Sub VisibleSheetsCount()
'UpdatebyKutoolsforExcel20150909
' https://www.extendoffice.com/documents/excel/3187-excel-count-visible-sheets.html
    Dim xSht As Variant
    Dim I As Long
    For Each xSht In ActiveWorkbook.Sheets
        If xSht.Visible Then I = I + 1
    Next
    MsgBox I & " sheets are visible", , "Kutools for Excel"
End Sub

Then do .Copy(After:=Sheets(I)) I think would work.