Set Page Break Lines using VBA

[Sam Kuo]

I'd like to set page break lines and print area in a worksheet using VBA, but
have no idea how to do this...

I have a worksheet with 11 pages (of the same size) when viewed in Page
Break Preview:
- Page 1 has a content table with 10 cells (B1:B10) where user can enter the
report titles.
- Page 2 shows contents under Title #1 (title name is entered in cell B1);
Page 3 for Title #2; and so on.

I'm hoping to only show and print Page 1, plus any other pages only if their
titles have been entered in the content table. (e.g. if only 3 titles are
entered, just show page 1 and the other 3 pages).

Any help would be appreciated (preferably with example and
explanation..because I'm just helpless with programming)

 

[Aran Black]

Sam,

I have written the following routine for you as per your specifications.
You need to insert page breaks at the end of each of your ranges first.
Let me know if you have any questions!
Aran
(Please rate this response)

Private Sub CommandButton1_Click()


var1 = Null
var2 = Null
var3 = Null
var4 = Null
var5 = Null
var6 = Null
var7 = Null
var8 = Null
var9 = Null
var10 = Null


If IsEmpty(Sheet1.Range("b1")) = True Then
Else
var1 = "$A$11:$f$20" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b2")) = True Then
Else
var2 = "$A$21:$f$30" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b3")) = True Then
Else
var3 = "$A$31:$f$40" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b4")) = True Then
Else
var4 = "$A$41:$f$50" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b5")) = True Then
Else
var5 = "$A$51:$f$60" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b6")) = True Then
Else
var6 = "$A$61:$f$70" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b7")) = True Then
Else
var7 = "$A$71:$f$80" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b8")) = True Then
Else

var8 = "$A$81:$f$90" & Chr(44)
End If
If IsEmpty(Sheet1.Range("b9")) = True Then
Else
var9 = "$A$91:$f$100" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b10")) = True Then
Else
var10 = "$A$101:$f$110"
End If

var11 = var1 & var2 & var3 & var4 & var5 & var6 & var7 & var8 & var9 & var10


ActiveSheet.PageSetup.PrintArea = var11


End Sub

 

[Sam Kuo]

Thank you Aran. Its great.

Sam,

I have written the following routine for you as per your specifications.
You need to insert page breaks at the end of each of your ranges first.
Let me know if you have any questions!
Aran
(Please rate this response)

Private Sub CommandButton1_Click()


var1 = Null
var2 = Null
var3 = Null
var4 = Null
var5 = Null
var6 = Null
var7 = Null
var8 = Null
var9 = Null
var10 = Null


If IsEmpty(Sheet1.Range("b1")) = True Then
Else
var1 = "$A$11:$f$20" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b2")) = True Then
Else
var2 = "$A$21:$f$30" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b3")) = True Then
Else
var3 = "$A$31:$f$40" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b4")) = True Then
Else
var4 = "$A$41:$f$50" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b5")) = True Then
Else
var5 = "$A$51:$f$60" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b6")) = True Then
Else
var6 = "$A$61:$f$70" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b7")) = True Then
Else
var7 = "$A$71:$f$80" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b8")) = True Then
Else

var8 = "$A$81:$f$90" & Chr(44)
End If
If IsEmpty(Sheet1.Range("b9")) = True Then
Else
var9 = "$A$91:$f$100" & Chr(44)
End If

If IsEmpty(Sheet1.Range("b10")) = True Then
Else
var10 = "$A$101:$f$110"
End If

var11 = var1 & var2 & var3 & var4 & var5 & var6 & var7 & var8 & var9 & var10


ActiveSheet.PageSetup.PrintArea = var11


End Sub