VBA printing question....is it possible to...

[amxtomzo]

Happy 4th of July, why am I at my office?
I was givin this code a while ago and it is great.....

Sub printout1()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Range("b8") <> "" Then
ws.PrintOut
End If
Next ws
End Sub

Now this code will only print out the pages with data on cell B8...
Can i modify this so it will also sort those pages based on the call value in F7.

I am hopeing that i can get the pages printed in highest to lowest value of cell F7, but only if there is a value in B8.

Thanks for your thoughts and help

Happy 4th

Thomas

 

[Ralajer]

Happy 4^th Thomas,

Try the below code and let me know if it works for you.

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

 

[amxtomzo]

Thank You Ralajer

that worked like a charm......
It did exactly what I asked for.....
However i should look before i leap some times

Is it possible to do this but select the work sheets I what to come out sorted.

there about 60+ sheets and they are grouped together

Could i do this sorting with sheets 1 through 10 then again with 11 through 29 ect...ect..??

I think thats more in line with what would save us some time each morning

Thomas

 

[Ralajer]

That doable
These two sub call the main sub and pass the left most sheet and the right most sheet names you want to sort and print.

Sub printSome1()
    sortedPrintFromTo "Sheet1", "Sheet10"
End Sub

Sub printSome2()
    sortedPrintFromTo "Sheet11", "Sheet29"
End Sub

This is just a modified version the previous which constrains the sheets to be sorted.

Sub sortedPrintFromTo(LeftSht As String, RightSht As String)

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

    j = 1
    
    For i = Sheets(LeftSht).Index To Sheets(RightSht).Index
        If Sheets(i).Range("B8").Value <> "" Then
            ReDim Preserve wsToPrint(1 To 2, 1 To j)
            wsToPrint(1, j) = Sheets(i).Index
            wsToPrint(2, j) = Sheets(i).Range("F7").Value
            j = j + 1
        End If
    Next i
    
    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

 

[amxtomzo]

Hi again Ralager

I am most likley missing something

I pasted your "sorted print from to" code into a module
the tryed to run it to see what happens, and the dialog box to select a Macro appears.

i then copied and pasted the "print some 1 " code in to an other module and put in a few of the works sheets i have, and i get "wrong nunber of arguments or invalid property assignment" box

i am on Office 2003 at here at work, would that be a reason?

thomas

 

[Ralajer]

I am not sure if it is an Excel version issue but I doubt it. Put all the code in the same module and run the printSome1 macro. Try that and if you get the same issue post the code from the printSome# macro you are trying to run.

The sortedPrintFromTo macro gets called from the printSome# macro and it won't work if called manually from the macro box.

The error message you posted suggest that there is something wrong the arguments being passed. Make sure to only have two arguments. The first being the left most sheet and second the right most sheet as they appear in the sheet tab bar at the bottom of Excel. The names must match the name in excel and be enclosed in quotes "name".

 

[amxtomzo]

thanks Ralager

I got it now, works absolutlely GREAT.

Just made shorter work of a daily routine for my people on the floor

Thanks again you have been most helpful

Thomas