Consulting

Results 1 to 18 of 18

Thread: Help with a VBA Print Range Code Problem

  1. #1
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    I am using the code listed below to automatically set my print ranges on my reports. The problem I am having is that if the document has no page breaks, the code works great, however if the report is Sub-Totaled and placing a page break in the document the code below will only print the first page.

    Any example, I have a document that have information for Buyers 1 through 10. I want to have a separate page for each buyer, so I Subtotal by Buyer and Check ?Page Break Between Groups?, again when I do the code will only print the first page, I need it to print the entire range of the document.

    [vba]Sub SetPrintRanges(StartCell As String)

    Dim i As Integer
    'Dim StartCell As String
    Dim CheckColumn As Integer

    'StartCell = "A12"

    For i = 1 To Sheets.Count
    CheckColumn = 0
    Sheets(i).Select
    Range(StartCell).Select
    Do Until CheckColumn = 4
    If ActiveCell.Value = vbNullString Then
    CheckColumn = CheckColumn + 1
    Else
    CheckColumn = 0
    End If
    ActiveCell.Offset(0, 1).Select
    Loop
    ActiveCell.Offset(0, -5).Select
    Do Until ActiveCell.Value = vbNullString
    ActiveCell.Offset(1, 0).Select
    Loop
    Worksheets(i).PageSetup.PrintArea = StartCell & ":" & Chr(64 + ActiveCell.Column) _
    & CStr(ActiveCell.Row - 1)
    Range(StartCell).Select
    Next i

    End Sub
    [/vba]
    Hope someone can help me out here.

    Thanks

    Jack

  2. #2
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi Jack and welcome to the board :hi

    Let see if we can help You with it but before moving on could You please upload an example of the workbook. I'm a little bit confused with some of the selections the code do.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  3. #3
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, Jack!

    I edited your code to show as code.
    Next time you post, check out the buttons above the message window. It's real cool.

    And... WELCOME!!
    Thrilled to have you here!
    ~Anne Troy

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Jack?

    Can we help you with this? Did you get it resolved?

    We'll delete this question if we don't hear back from you by 17 June 2004.
    Moved to the recycle bin until we hear back from the asker.

    Thanks!
    ~Anne Troy

  5. #5
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    Sorry,

    Have been very Busy. Works Fine.


    Thanks

  6. #6
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    Sorry,

    When I used this code it set the print range to the first line break. The Print Range will not get past the page break in order to cover the rest of the report.

    Any help Again would be thankful.


    Jack

  7. #7
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location

    Page Break VBA Code Help

    The Code listed below automatically will set the print Range for the entire document. I have documents that the Sub Total is used and Page Breaks is Selected. The problem with the code listed below is that it will only Set the Print Range until the first Line Break then Stop.

    Sure would like to not have to manually set the Print Range on each of these reports.


    Thanks


    Jack

    [VBA]
    Sub SetPrintRanges(StartCell As String)

    ' Code automaticalls sets print Ranges in Reports
    ' Updated 5/21/04 0817

    Dim i As Integer
    'Dim StartCell As String
    Dim CheckColumn As Integer

    ' StartCell = "A12"

    For i = 1 To Sheets.Count
    CheckColumn = 0
    Sheets(i).Select
    Range(StartCell).Select
    Do Until CheckColumn = 4 'Check column = # of blank columns
    If ActiveCell.Value = vbNullString Then
    CheckColumn = CheckColumn + 1
    Else
    CheckColumn = 0
    End If
    ActiveCell.Offset(0, 1).Select
    Loop
    ActiveCell.Offset(0, -5).Select 'If above is changed this must be 1 more than Column Count
    Do Until ActiveCell.Value = vbNullString
    ActiveCell.Offset(1, 0).Select
    Loop
    Worksheets(i).PageSetup.PrintArea = StartCell & ":" & Chr(64 + ActiveCell.Column) _
    & CStr(ActiveCell.Row - 1)
    Range(StartCell).Select
    Next i

    End Sub
    [/VBA]

  8. #8
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Try changing this
    [vba]Worksheets(i).PageSetup.PrintArea = StartCell & ":" & _
    Chr(64 + ActiveCell.Column) & CStr(ActiveCell.Row - 1)[/vba]
    to this
    [vba]worksheets(i).pagesetup.printArea = startcell &":" & _
    worksheet(i).range.SpecialCells(xlCellTypeLastCell).Address[/vba]


    HTH
    The most difficult errors to resolve are the one's you know you didn't make.


  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    i.e.

    [vba]
    Sub SetPrintRanges(StartCell As String)

    For i = 1 To Sheets.Count
    Worksheets(i).PageSetup.PrintArea = startcell & ":" & _
    Worksheet(i).Cells.SpecialCells(xlCellTypeLastCell).Address
    Next i

    End Sub
    [/vba]

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  10. #10
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    I have tryed both codes. I am using this VBA Code from a Call and receiving a Stop on the Second "Worksheet(i)" part of the code.

    an Example of how I am using the code,
    [VBA]
    Call SetPrintRanges("A12")[/VBA]


    Thanks


    Jack

  11. #11
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Sorry, should be this.

    [vba]worksheets(i).pagesetup.printArea = startcell &":" & _
    worksheets(i).range.SpecialCells(xlCellTypeLastCell).Address[/vba]

    I think 007 and I made the same mistake.

    It should be Worksheets not worksheet

    Great minds screw up alike?
    The most difficult errors to resolve are the one's you know you didn't make.


  12. #12
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    When I enter the fix I get the following,

    [vba]Sub SetPrintRanges(StartCell As String)

    ' Code automaticalls sets print Ranges in Reports
    ' Updated 5/21/04 0817

    Dim i As Integer
    'Dim StartCell As String
    Dim CheckColumn As Integer

    ' StartCell = "A12"

    For i = 1 To Sheets.Count
    CheckColumn = 0
    Sheets(i).Select
    Range(StartCell).Select
    Do Until CheckColumn = 4 'Check column = # of blank columns
    If ActiveCell.Value = vbNullString Then
    CheckColumn = CheckColumn + 1
    Else
    CheckColumn = 0
    End If
    ActiveCell.Offset(0, 1).Select
    Loop
    'If above is changed this must be 1 more than Column Count
    ActiveCell.Offset(0, -5).Select
    Do Until ActiveCell.Value = vbNullString
    ActiveCell.Offset(1, 0).Select
    Loop
    Worksheets(i).PageSetup.PrintArea = StartCell & ":" & _
    Worksheets(i).Range.SpecialCells(xlCellTypeLastCell).Address
    Next i

    End Sub[/vba]

  13. #13
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    I wanted it to display the line we have been working on, it is yellowed out, sorry for the misformatting.


    Jack

  14. #14
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    Great minds screw up alike?
    They do when they copy and paste others code LOL

    Does this not work?

    [vba]
    Sub SetPrintRanges(StartCell As String)

    For i = 1 To WorkSheets.Count

    Worksheets(i).PageSetup.PrintArea = StartCell & ":" & _

    Worksheets(i).Cells.SpecialCells(xlCellTypeLastCell).Address

    Next i

    End Sub
    [/vba]

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  15. #15
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    I ran a quick test, my code didn't work, but 007's did.
    [VBA]
    ws.PageSetup.PrintArea = "A1:" & ws.Cells.SpecialCells(xlCellTypeLastCell).Address
    [/VBA]
    So change the .range to .cells and it should resolve your problem.
    The most difficult errors to resolve are the one's you know you didn't make.


  16. #16
    VBAX Regular Jack58's Avatar
    Joined
    May 2004
    Location
    MN
    Posts
    30
    Location
    When I use 007's Code on my Spreadsheet I get the following results,

    Data Ranges from Column A to Column J
    With 0007's code Column A to Column K

    Also 6 pages is what the pages Breaks show and with the code it is extending to 7 Pages.


    If someone would like me to foward the file for review please let me know.


    Thanks for all the help here today.



    Jack J.

  17. #17
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Jack,
    You can attach a file to a post, just press the go advanced button below the quick reply box, and then press the manage attachments button, in the additional options section. Attach your file there.

    PS-It sounds as though you might have some hidden formating on the sheet. This will cause the last used cell to get selected incorrectly.
    The most difficult errors to resolve are the one's you know you didn't make.


  18. #18
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I do believe these all belonged to the same problem, so I've merged the two threads.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •