Solved

Excel VBA

Posted on 2015-01-07
8
73 Views
Last Modified: 2015-01-20
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
Comment
Question by:AXISHK
  • 4
  • 4
8 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40537393
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
 

Author Comment

by:AXISHK
ID: 40557597
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40557641
change line 7 to

Rounding = 80
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

Author Comment

by:AXISHK
ID: 40557652
Doesn't help... Tks
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40557675
Could you be more precise?
0
 

Author Comment

by:AXISHK
ID: 40559154
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40559211
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
 

Author Closing Comment

by:AXISHK
ID: 40561245
That's work perfect. Tks
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question