Need to change a VBA code to rename new worksheet based on a cell value

Hi Patrick

The error message is clear

You are trying to name your new sheet with an invalid name or chart

In your scenario B2=":"      (Red arrow pointing)

I noticed on your picture, the cell B7 is selected, (Blue arrow pointing)

I just wonder if that's the cell you want to name the new sheet after?

If so, cell B7 also exceed the 31 chart limit to name a sheet in excel.

So, for either cell B2 or B7 you must check the Sheet name nomenclature.

Hope this helps

Regards

Jeovany

Thank you both. I have re-run the Macro and the photos show outputs.

In essence the macro does the following:

1. breaks down the worksheet 'TEST' (the one that had initially 120 tables one after another..) and keeps in the first table. CORRECT
2. creates a second worksheet with the name 'Value added gross(1)' which is picked on cell B2. CORRECT
3. creates a third worksheet with the name "Sheet3' which has all the remaining tables. INCORRECT

From my perspective:

a) the 53 rows as range for each table is correct.

b) cell B2 of each new worksheet should name the worksheet itself

Sorry but I am not an expert in Macros so I might be missing the obvious?

Thanks

Patrick

Hi Patrick

The error message is clear

You are trying to name your new sheet with an invalid name or chart

In your scenario B2=":"      (Red arrow pointing)

I noticed on your picture, the cell B7 is selected, (Blue arrow pointing)

I just wonder if that's the cell you want to name the new sheet after?

If so, cell B7 also exceed the 31 chart limit to name a sheet in excel.

So, for either cell B2 or B7 you must check the Sheet name nomenclature.

Hope this helps

Regards

Jeovany

You mention that you want to name the sheet after the contents of cell B2. But B2 is empty...

Does this do what you want?

Sub SplitWorksheet()

    Dim lngLastRow As Long

    Dim lngNumberOfRows As Long

    Dim lngI As Long

    Dim currSheet As Worksheet

    Dim prevSheet As Worksheet

    'Number of rows to split among worksheets

    lngNumberOfRows = 53

    'Current worksheet in workbook

    Set prevSheet = ThisWorkbook.ActiveSheet

    'Number of rows in worksheet

    lngLastRow = prevSheet.Cells(Rows.Count, 1).End(xlUp).Row

    'Worksheet counter for added worksheets

    lngI = 1

    Do While lngLastRow > lngNumberOfRows

        Set currSheet = ThisWorkbook.Worksheets.Add

        With currSheet

            .Move after:=Worksheets(Worksheets.Count)

            prevSheet.Rows(lngNumberOfRows + 1 & ":" & lngLastRow).EntireRow.Cut .Range("A1")

            .Name = .Range("B2").Value & "(" & lngI & ")"

            lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        End With

        Set prevSheet = currSheet

        lngI = lngI + 1

    Loop

End Sub

Hi thanks.

Run your amended Macro which seems to work only for the first sheet and then stops at the second. See below.

My apologies probably I didn't explain myself correctly on what  i want to achieve - see steps below:

1. I have a worksheet which circa 120 tables listed one after another
2. I need to run a macro that breaks down the above 120 tables into 120 worksheets
3. each worksheet has to be named differently according to the B2 cell value available in each of the new 120 worksheets. This means that (1) the macro has to create a new worksheet (in total ~120+) and (2) it has to name each worksheet with the words avaialble within cell B2 of the new worksheet.

Hope it makes sense. And thanks again for your help. 

Does this do what you want?

Sub SplitWorksheet()

    Dim lngLastRow As Long

    Dim lngNumberOfRows As Long

    Dim lngI As Long

    Dim currSheet As Worksheet

    Dim prevSheet As Worksheet

    'Number of rows to split among worksheets

    lngNumberOfRows = 53

    'Current worksheet in workbook

    Set prevSheet = ThisWorkbook.ActiveSheet

    'Number of rows in worksheet

    lngLastRow = prevSheet.Cells(Rows.Count, 1).End(xlUp).Row

    'Worksheet counter for added worksheets

    lngI = 1

    Do While lngLastRow > lngNumberOfRows

        Set currSheet = ThisWorkbook.Worksheets.Add

        With currSheet

            .Move after:=Worksheets(Worksheets.Count)

            prevSheet.Rows(lngNumberOfRows + 1 & ":" & lngLastRow).EntireRow.Cut .Range("A1")

            .Name = .Range("B2").Value & "(" & lngI & ")"

            lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        End With

        Set prevSheet = currSheet

        lngI = lngI + 1

    Loop

End Sub