Change name of worksheet in VBA LIKE Sheet%

Question

I have to split the main excel worksheet into worksheets with 100 rows each.

I have the code running the correct data into each worksheet but I need to rename the worksheets.

They are currently output as "Sheet1", "Sheet2" etc.

I would like the code to be dynamic as I don't know how many worksheets will be created.

I would either like to include the renaming in the loop "J1", "J2" etc. or running a piece of code afterwards that renames any workbook like "Sheet%" to "J%"

My current code is as follows:

Option Explicit

Sub SplitDataNrows()

Dim N As Long, rw As Long, LR As Long, Titles As Boolean
    N = 100
    Titles = True
    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For rw = 1 + ---Titles To LR Step N
            Sheets.Add
            If Titles Then
                .Rows(1).Copy Range("A1")
                .Range("A" & rw).Resize(N).EntireRow.Copy Range("A2")
            Else
                .Range("A" & rw).Resize(N).EntireRow.Copy Range("A1")
            End If
            Columns.AutoFit
        Next rw
        .Activate
    End With
    Application.ScreenUpdating = True

End Sub

set w=Sheets.Add:w.name="TESTING_" & int((rw+N)/N) something like that? — Nathan_Sav

ZMannion ZMannion · Accepted Answer · 2019-11-18T14:51:46

Corrected:

Option Explicit

Sub SplitDataNrows()

Dim N As Long, rw As Long, LR As Long, Titles As Boolean
    N = 100
    Titles = True
    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For rw = 1 + ---Titles To LR Step N
            Sheets.Add.Name = "J" & Int((rw + N) / N)
            If Titles Then
                .Rows(1).Copy Range("A1")
                .Range("A" & rw).Resize(N).EntireRow.Copy Range("A2")
            Else
                .Range("A" & rw).Resize(N).EntireRow.Copy Range("A1")
            End If
            Columns.AutoFit
        Next rw
        .Activate
    End With
    Application.ScreenUpdating = True

End Sub

Change name of worksheet in VBA LIKE Sheet%

1 Answers