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.

Code:

---

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

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

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!

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!

Sorry,

Have been very Busy. Works Fine.


Thanks

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

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

Code:

---

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

---

Try changing this

Code:

---

Worksheets(i).PageSetup.PrintArea = StartCell & ":" & Chr(64 + ActiveCell.Column) & CStr(ActiveCell.Row - 1)

---

to this

Code:

---

worksheets(i).pagesetup.printArea = startcell &":" & worksheet(i).range.SpecialCells(xlCellTypeLastCell).Address

---

HTH

i.e.

Code:

---

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

---

:)

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,

Code:

---

Call SetPrintRanges("A12")

---

Thanks


Jack

Sorry, should be this.

Code:

---

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?

When I enter the fix I get the following,

Code:

---

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

---

I wanted it to display the line we have been working on, it is yellowed out, sorry for the misformatting.


Jack

Quote:

---

Great minds screw up alike?

---

They do when they copy and paste others code LOL

Does this not work?

Code:

---

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

---

:)

I ran a quick test, my code didn't work, but 007's did.

Code:

---

ws.PageSetup.PrintArea = "A1:" & ws.Cells.SpecialCells(xlCellTypeLastCell).Address

---

So change the .range to .cells and it should resolve your problem.

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.

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.

I do believe these all belonged to the same problem, so I've merged the two threads.