Consulting

Results 1 to 18 of 18

Thread: Sleeper: Help with a VBA Print Range Code Problem

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

    Sleeper: Help with a VBA Print Range Code Problem

    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.

    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

    Hope someone can help me out here.

    Thanks

    Jack
    Last edited by Aussiebear; 04-29-2023 at 05:28 PM. Reason: Adjusted the code tags

  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


    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
    Last edited by Aussiebear; 04-29-2023 at 06:44 PM. Reason: Adjusted the code tags

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

    HTH
    Last edited by Aussiebear; 04-29-2023 at 06:45 PM. Reason: Adjusted the code tags
    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
    622
    Location
    i.e.

    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

    Last edited by Aussiebear; 04-29-2023 at 06:46 PM. Reason: Adjusted the code tags
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  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,

    Call SetPrintRanges("A12")

    Thanks


    Jack
    Last edited by Aussiebear; 04-29-2023 at 06:46 PM. Reason: Adjusted the code tags

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

    worksheets(i).pagesetup.printArea = startcell &":" & worksheets(i).range.SpecialCells(xlCellTypeLastCell).Address
    I think 007 and I made the same mistake.

    It should be Worksheets not worksheet

    Great minds screw up alike?
    Last edited by Aussiebear; 04-29-2023 at 06:47 PM. Reason: Adjusted the code tags
    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,

    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
    Last edited by Aussiebear; 04-29-2023 at 06:49 PM. Reason: Adjusted the code tags

  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
    622
    Location
    Great minds screw up alike?
    They do when they copy and paste others code LOL

    Does this not work?


    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

    Last edited by Aussiebear; 04-29-2023 at 06:50 PM. Reason: Adjusted the code tags
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  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.

    ws.PageSetup.PrintArea = "A1:" & ws.Cells.SpecialCells(xlCellTypeLastCell).Address
    So change the .range to .cells and it should resolve your problem.
    Last edited by Aussiebear; 04-29-2023 at 06:51 PM. Reason: Adjusted the code tags
    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
  •