PDA

View Full Version : Help with a VBA Print Range Code Problem



Jack58
05-21-2004, 10:36 AM
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

XL-Dennis
05-21-2004, 11:06 AM
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

Anne Troy
05-21-2004, 12:39 PM
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
06-03-2004, 10:06 AM
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!

Jack58
06-28-2004, 08:46 AM
Sorry,

Have been very Busy. Works Fine.


Thanks

Jack58
06-28-2004, 09:01 AM
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

Jack58
06-30-2004, 05:53 AM
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

CBrine
06-30-2004, 06:05 AM
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

mark007
06-30-2004, 06:11 AM
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


:)

Jack58
06-30-2004, 07:20 AM
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

CBrine
06-30-2004, 07:30 AM
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?

Jack58
06-30-2004, 07:43 AM
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

Jack58
06-30-2004, 07:45 AM
I wanted it to display the line we have been working on, it is yellowed out, sorry for the misformatting.


Jack

mark007
06-30-2004, 07:54 AM
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


:)

CBrine
06-30-2004, 08:12 AM
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.

Jack58
06-30-2004, 01:27 PM
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.

CBrine
06-30-2004, 01:49 PM
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.

Anne Troy
07-01-2004, 05:51 PM
I do believe these all belonged to the same problem, so I've merged the two threads.