VBA code for printing range(s)

amxtomzo

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

i was given this code some time ago and it works great...
Code:
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...

Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
thanks for helping...

i was given this code some time ago and it works great...
Code:
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...

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,071
Latest member
hijackhippo

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
Top