• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

Excel VBA

I have written a VBA that extract records from database, ranged from column A to H with few hunderds of rows.

How to configure the page break with VBA such that
- column A - H always fix in a A4 width
- a single A4 size will have 40 rows only , and page break to a new page.

Tks
0
AXISHK
Asked:
AXISHK
  • 4
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try


Sub Macro()
Set sh = ActiveSheet

sh.PageSetup.Zoom = False
sh.ResetAllPageBreaks
sh.PageSetup.Orientation = xlLandscape
Rounding = 40
LastRowRounded = WorksheetFunction.MRound(Range("A" & Cells.Rows.Count).End(xlUp).Row, Rounding)
sh.PageSetup.PrintArea = "A1:H" & LastRowRounded
sh.PageSetup.FitToPagesWide = 1
sh.PageSetup.FitToPagesTall = LastRowRounded / Rounding
 
sh.PrintPreview
Set sh = Nothing
End Sub

Open in new window

Regards
0
 
AXISHKAuthor Commented:
Tks. It can't really fit into the row that I need, say every 1-80, 81-160,161- 240 and 241-320 into separate page. Any idea ?

Tks
0
 
Rgonzo1971Commented:
change line 7 to

Rounding = 80
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AXISHKAuthor Commented:
Doesn't help... Tks
0
 
Rgonzo1971Commented:
Could you be more precise?
0
 
AXISHKAuthor Commented:
I have extracted a coding from my marco. Base on the calculation, page break should occur every 80 rows but it doesn't. Any idea ?

Tks



Rounding = iNumberOfRow * lblHeight
   80 =    4   x 20


LastRowRounded = iTotalRecord / iNumberOfCol * lblHeight
      300 =     60 / 4 *20

stPrintArea = Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ":" & Cells(LastRowRounded, iNumberOfCol * lblWidth).Address(RowAbsolute:=False, ColumnAbsolute:=False)
                                                                                                               (4 * 3)
wksheet.PageSetup.FitToPagesWide = 1
wksheet.PageSetup.FitToPagesTall = LastRowRounded / Rounding
                                       300 / 80
'wksheet.PrintPreview
Set wksheet = Nothing
Test.xlsx
0
 
Rgonzo1971Commented:
pls try

Sub Macro()
Set sh = ActiveSheet

sh.ResetAllPageBreaks
sh.PageSetup.Zoom = 25

sh.PageSetup.Orientation = xlLanscape
Rounding = 40
LastRowRounded = WorksheetFunction.RoundUp(Range("A" & Cells.Rows.Count).End(xlUp).Row / Rounding, 0) * Rounding
sh.PageSetup.PrintArea = "A1:L" & LastRowRounded
For Idx = 1 To LastRowRounded / Rounding - 1
    myRow = Rounding * Idx + 1
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & myRow)
Next
    sh.PageSetup.Zoom = False
    sh.PageSetup.FitToPagesWide = 1
    sh.PageSetup.FitToPagesTall = False
 
sh.PrintPreview
Set sh = Nothing
End Sub

Open in new window

TestV1.xlsm
0
 
AXISHKAuthor Commented:
That's work perfect. Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now