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![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Code provided previously
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
VBA Code:
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
Last edited by a moderator: