Make a new worksheet at every page break in a worksheet & name each sheet after a value from a cell.

[Arcomvi]

Hi
Thank you for the code previously posted John_W, it works great however, Im hoping you can help with an additional problem to this. I need each tab to be labeled based on a value within a cell on each new speadsheet. There is a catch, the cell that has the data is always on a different row but only ever in one column ie; H
I look forward to your help
Code provided previously

Public Sub Copy_Each_Page_Break_Section_To_New_Worksheet()

    Dim reportWorksheet As Worksheet
    Dim saveActiveCell As Range
    Dim lastRow As Long, pageStartRow As Long
    Dim page As Long
    Dim newWorksheet As Worksheet
   
    'Look on the active sheet in the active workbook
   
    Set reportWorksheet = ActiveWorkbook.ActiveSheet
    Set saveActiveCell = ActiveCell
   
    With reportWorksheet
       
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        pageStartRow = 1
       
        'Copy rows in each page break section to new worksheet
       
        For page = 1 To .HPageBreaks.Count           
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            newWorksheet.Name = "Page " & page
            .Rows(pageStartRow & ":" & .HPageBreaks(page).Location.Row - 1).EntireRow.Copy newWorksheet.Range("A1")           
            pageStartRow = .HPageBreaks(page).Location.Row
        Next
   
        If pageStartRow <= lastRow Then
       
            'Copy rows after last page break to new worksheet
           
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            newWorksheet.Name = "Page " & page
            .Rows(pageStartRow & ":" & lastRow).EntireRow.Copy newWorksheet.Range("A1")
       
        End If
   
    End With
   
    'Restore active cell
   
    reportWorksheet.Activate
    saveActiveCell.Select
   
End Sub

 

[Engberg]

Should be easy, as long as we can determine which cell in H to look at. Is the cell special compared to all the other cells in H in some way? For example, is it always the only cell with a value in H? Or can the cell to look at only contain a certain set of values that can't appear anywhere else in H? Or is the cell a named range? A picture of your sheets might help.

 

[Arcomvi]

Should be easy, as long as we can determine which cell in H to look at. Is the cell special compared to all the other cells in H in some way? For example, is it always the only cell with a value in H? Or can the cell to look at only contain a certain set of values that can't appear anywhere else in H? Or is the cell a named range? A picture of your sheets might help.

Thankfully, it is the only cell with data in column H. Unfortunately, the spreadsheet has no gridlines nd is formatted in a very strange fashion. This is an output from a SAP type system. Sorry I couldn't be any more help. Oh, the contents of the cell is formatted as "general" and can be txt, numbers or combination.

 

[Engberg]

Replace your For-loop and the If-statement with these, if you just want to name the sheets the same value on the H-cell. You never specified how you wanted to name it though?

        For page = 1 To .HPageBreaks.Count
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            .Rows(pageStartRow & ":" & .HPageBreaks(page).Location.Row - 1).EntireRow.Copy newWorksheet.Range("A1")
            pageStartRow = .HPageBreaks(page).Location.Row
            newWorksheet.Name = "Page " & newWorksheet.Range("H" & newWorksheet.UsedRange.Rows.Count).End(xlUp).Value
        Next
 
        If pageStartRow <= lastRow Then
     
            'Copy rows after last page break to new worksheet
         
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            .Rows(pageStartRow & ":" & lastRow).EntireRow.Copy newWorksheet.Range("A1")
            newWorksheet.Name = "Page " & newWorksheet.Range("H" & newWorksheet.UsedRange.Rows.Count).End(xlUp).Value
     
        End If

 

[Arcomvi]

Replace your For-loop and the If-statement with these, if you just want to name the sheets the same value on the H-cell. You never specified how you wanted to name it though?

        For page = 1 To .HPageBreaks.Count
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            .Rows(pageStartRow & ":" & .HPageBreaks(page).Location.Row - 1).EntireRow.Copy newWorksheet.Range("A1")
            pageStartRow = .HPageBreaks(page).Location.Row
            newWorksheet.Name = "Page " & newWorksheet.Range("H" & newWorksheet.UsedRange.Rows.Count).End(xlUp).Value
        Next
 
        If pageStartRow <= lastRow Then
    
            'Copy rows after last page break to new worksheet
        
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            .Rows(pageStartRow & ":" & lastRow).EntireRow.Copy newWorksheet.Range("A1")
            newWorksheet.Name = "Page " & newWorksheet.Range("H" & newWorksheet.UsedRange.Rows.Count).End(xlUp).Value
    
        End If

Thank you To give you an idea on names for the tabs, I have a spreadsheet list, heres a snippet;-

 

[Arcomvi]

Thank you To give you an idea on names for the tabs, I have a spreadsheet list, heres a snippet;-

View attachment 113844

I ran the new code and it worked great, although I didnt require the word "page" so removed it from the code. The code has highlighted a new issue though. Unfortunately, although the code works correctly, some of the pages are extensions of the previous page (tab name) and therefore doesnt have a "H" value present. Is there a way in the code where if it cannot see any value in "H" column it will ignore and dont create a tab? Thanks

 

[Arcomvi]

I ran the new code and it worked great, although I didnt require the word "page" so removed it from the code. The code has highlighted a new issue though. Unfortunately, although the code works correctly, some of the pages are extensions of the previous page (tab name) and therefore doesnt have a "H" value present. Is there a way in the code where if it cannot see any value in "H" column it will ignore and dont create a tab? Thanks

Sorry, realised that I needed to edit my sentence but ran out of time. If a value isnt present in "H" then add the page to the current tab, and so on.