We help IT Professionals succeed at work.

Page setup with Excel VBA

AXISHK
AXISHK asked
on
522 Views
Last Modified: 2012-02-19
Any idea how to write VBA code to ensure Column A - M to fit in a page while the external row could be print on the second page ? In addition, I need to align the paper in Landscape with Row 1-3 be repeated in additional page ?

Thanks
Comment
Watch Question

Try this. Change 99 in
 .FitToPagesTall = 99
if your printout exceed 99 pages

Sub PrintSetup()
'
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$3"
        .PrintArea = "$A:$M"
        .Orientation = xlLandscape
    End With
    With ActiveSheet.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 99
    End With

End Sub

Open in new window


Regards,
Curt
Most Valuable Expert 2012
Top Expert 2012

Commented:
>>while the external row could be print on the second page ?

epalcm has a good tip, there.  However, what do you mean by the comment, above?

Dave

Author

Commented:
Thanks, but column J to M go to another page and I need to manually fit the columns to put them into a single page. Any idea ?

Thanks
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's a link to a tip that I did recently that fit columns to a minimum number of  page.  Check it out, even download the example.  Is this what you're trying to do? https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27581613.html

In your instance it would be ensuring that the columns, as-is, could print at 100%, but reduce their column width until they do?  Alternatively, you just need to do a fit to page programmatically as Curt did for the first post (above)?

If its the latter, I'll wait as I believe Curt will have a response for that.

Dave
Here's my test file including the macro and it appears to do what you ask for. Maybe I don't understand what you really want? Did you try the macro in your file? It is possible to upload a sample.

Regards,
Curt
PrintSetup-Sample.xlsm
I updated the file a little. I added a button which starts the macro. I also added a Print preview at the end of the macro to bring up the resulting printout for you to review.

Regards,
Curt
PrintSetup-Sample-2.xlsm

Author

Commented:
I still couldn't fix all the columns in to a page. Any idea ?
I have attached my file for your reference.

Thanks
test.xls
Sorry, I left out one line in the code that is required.

.Zoom = False

Sub RtnData()

'Setup Printing area
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$7"
        .PrintArea = "$A:$M"
        .Orientation = xlLandscape
    End With

With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 99
End With
 

End Sub

Open in new window


Regards,
Curt
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Tls

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.