CODEWITHSUNDEEP
excelvba
ZiggyStarlust
ZiggyStarlust

Reputation: 37

Dynamic page breaks

New to the forum. New to VBA.

I have a sheet(Sheet1), which accesses a master sheet. Within Sheet1, in F1, there is a dropdown menu that gives you an option to select a number from 1-27. The information in Sheet1 changes based on the selection of a number.

The problem: The size of the sheet changes for every number selected. I'm trying to figure out a way to automatically set page breaks based on the number in F1.

This is what I have so far, but it doesn't seem to work:

Sub PageBreaks()
On Error Resume Next
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

If "F1" = 1 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(85)
ElseIf "F1" = 2 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(85)
ElseIf "F1" = 3 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(87)
ElseIf "F1" = 4 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(88)
ElseIf "F1" = 5 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(85)
ElseIf "F1" = 6 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(85)
ElseIf "F1" = 7 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 8 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 9 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 10 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 11 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 12 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 13 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 14 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 15 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 16 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 17 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 18 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 19 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 20 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 21 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 22 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 23 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 24 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 25 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(43)
    ActiveSheet.HPageBreaks.Add Before:=Rows(92)
ElseIf "F1" = 26 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(59)
ElseIf "F1" = 27 Then
    ActiveSheet.HPageBreaks.Add Before:=Rows(59)

End If
End Sub

Any help would be greatly appreciated!

Upvotes: 1

Views: 994

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try the following code, I've added Some of your Cases (not all of them).

I think you try to add the rest. (some of your cases overlap).

Sub PageBreaks()

On Error Resume Next

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

Select Case Range("F1").Value
    Case 1, 2, 5
        ActiveSheet.HPageBreaks.Add Before:=Rows(43)
        ActiveSheet.HPageBreaks.Add Before:=Rows(85)
    Case 3
        ActiveSheet.HPageBreaks.Add Before:=Rows(43)
        ActiveSheet.HPageBreaks.Add Before:=Rows(87)
    Case 4
        ActiveSheet.HPageBreaks.Add Before:=Rows(43)
        ActiveSheet.HPageBreaks.Add Before:=Rows(88)

    ' add here the rest of your cases, some of the overlap
    'Case ....

End Select

End Sub

Upvotes: 2

Related Questions