VBA code for printing range(s)

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
312
thanks for helping...

i was given this code some time ago and it works great...
PHP:
Sub sortedPrint()

    Dim ws As Worksheet
    Dim wsToPrint() As Double
    Dim pCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Max As Integer

    i = 1
    For Each ws In Worksheets
        If ws.Range("B8").Value <> "" Then
            ReDim Preserve wsToPrint(1 To 2, 1 To i)
            wsToPrint(1, i) = ws.Index
            wsToPrint(2, i) = ws.Range("F7").Value
            i = i + 1
        End If
    Next ws
    
    pCount = UBound(wsToPrint, 2)

    For i = 1 To pCount
        Max = 1
        For j = 1 To pCount
            If wsToPrint(2, j) > wsToPrint(2, Max) Then Max = j
        Next j
        Sheets(wsToPrint(1, Max)).PrintOut
        wsToPrint(2, Max) = -9.9E+101
    Next i
End Sub

At the time i needed to then sort the printing through several ranges...
and was given this, i changed teh ranges and used it 10 times is a workbook
and it works great too...

PHP:
Sub printSome1()
    sortedPrintFromTo "sheet1", "sheet8"
End Sub
Sub printSome2()
    sortedPrintFromTo "sheet10", "Wtrcarr28"
End Sub

and i use that PrintSome about 10 times.......

OK, here is my problem
i am building the same workbook for an other new customer
and the printing is just not working
there is a Compile Error: "Sub or function not defined"

i think my problem is in the second to the last line
in the first code where is states....
"wsToPrint(2, Max) = -9.9E+101"

because of the number of pages, i am guessing at the problem

the first worksheet that should print is the 12th
and there is a total of 71 worksheets to look through


this code works great in one work book but not the new one i am making
i need to print.....
sorted by cell F7, from highest to lowest number
with in a rage of worksheets
if there is a value ( any value ) in cell B8

i thought this would be an easy copy and paste into this new work book
( everything else was )

thanks for your help as always

Thomas
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
thanks for helping...

i was given this code some time ago and it works great...
PHP:
Sub sortedPrint()

    Dim ws As Worksheet
    Dim wsToPrint() As Double
    Dim pCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Max As Integer

    i = 1
    For Each ws In Worksheets
        If ws.Range("B8").Value <> "" Then
            ReDim Preserve wsToPrint(1 To 2, 1 To i)
            wsToPrint(1, i) = ws.Index
            wsToPrint(2, i) = ws.Range("F7").Value
            i = i + 1
        End If
    Next ws
    
    pCount = UBound(wsToPrint, 2)

    For i = 1 To pCount
        Max = 1
        For j = 1 To pCount
            If wsToPrint(2, j) > wsToPrint(2, Max) Then Max = j
        Next j
        Sheets(wsToPrint(1, Max)).PrintOut
        wsToPrint(2, Max) = -9.9E+101
    Next i
End Sub

At the time i needed to then sort the printing through several ranges...
and was given this, i changed teh ranges and used it 10 times is a workbook
and it works great too...

PHP:
Sub printSome1()
    sortedPrintFromTo "sheet1", "sheet8"
End Sub
Sub printSome2()
    sortedPrintFromTo "sheet10", "Wtrcarr28"
End Sub

and i use that PrintSome about 10 times.......

OK, here is my problem
i am building the same workbook for an other new customer
and the printing is just not working
there is a Compile Error: "Sub or function not defined"

i think my problem is in the second to the last line
in the first code where is states....
"wsToPrint(2, Max) = -9.9E+101"

because of the number of pages, i am guessing at the problem

the first worksheet that should print is the 12th
and there is a total of 71 worksheets to look through


this code works great in one work book but not the new one i am making
i need to print.....
sorted by cell F7, from highest to lowest number
with in a rage of worksheets
if there is a value ( any value ) in cell B8

i thought this would be an easy copy and paste into this new work book
( everything else was )

thanks for your help as always

Thomas


The posted code appears to work. Check for typos and spelling in your new workbook code. The line that you thought might be the problem did not seem to interfere with the calculation for the printing sequence. That funny looking number is just a scientific notation, but it really does nothing to the calculation process.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top