Help needed in Excel

[cssriraman]

Hi,

I have a number of excel sheets that are not formatted and vary in the
number of pages.

I would like to have them landscaped and have a page break inserted after
the “Page” number.

The first page does not contain a “Page” number.

Could you please provide me with a working sample of code to accomplish
this task.

Thank you in advance.

[Bruce Fox]

Option Explicit

Private Sub Whatever()
Dim shtSheet As Worksheet

For Each shtSheet In Worksheets

'Part A:
shtSheet.PageSetup.Orientation = xlLandscape
'Part B:
If shtSheet.Index <> 1 Then shtSheet.Rows(25).PageBreak = xlPageBreakManual

Next
End Sub


I had a guess where the "Page Number" may be.... (Eg row 25) You can tweek as required. :)

[cssriraman]

Here is the sample file uploaded. Please help.

[DKenny]

Cssriraman

I think I've got what you need. Rather than using the page number as the identifier, I used the title of the next page "ABC QUOTING CENTER" " as the placeholder to determine where to insert the page breaks. Here's the procedure that adds the breaks and a sample proc showing how its used.

Let me know if this works.

Option Explicit


Sub FormatReport(SheetName As String, TitleColumn As Long)
Dim wksSheet As Worksheet
Dim rngSearch As Range
Dim lRowCount As Long
Dim lRowNum As Long

On Error Resume Next
Set wksSheet = ThisWorkbook.Worksheets(SheetName)
On Error GoTo 0

'Trap for cases where the sheet nmae doesn't exist
If wksSheet Is Nothing Then Exit Sub

With wksSheet
'Remove current breaks
.ResetAllPageBreaks

With .PageSetup
'Set page as landscape and 1 page wide
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Column where we search for the page title
Set rngSearch = .Columns(TitleColumn)

'Number of rows to check for page headers
lRowCount = .UsedRange.Rows.Count
End With

'Ingnore the first page by starting at row 3
For lRowNum = 3 To lRowCount

With rngSearch.Cells(lRowNum, 1)

'When the title exist in the row...
If .Value = "ABC QUOTING CENTER " Then

'...insert a break one row up
.Offset(-1, 0).PageBreak = xlPageBreakManual
End If

End With

Next lRowNum

'Clear Object Variables
Set wksSheet = Nothing
End Sub

'Here's the code in action
Sub sample()
FormatReport "Close Ratio by Customer", 8
FormatReport "Quotation Activity", 11
End Sub