Are you trying to print 55 different worksheets or are you trying to print 55
different pages all from the same worksheet?
I read your question as one worksheet.
If that's true, then you could set up a worksheet with:
Row 1: Titles
A2:A56: Names of the ranges on that single worksheet
B2:B56: A cell used to contain a checkmark (more on that later).
C2:C56: Name
D2
56: SSN
E2:E56: whatever you want.
I'd hide column A (used for development--not by the user).
And I'd format B2:B56 in a nice way:
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252
But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.
It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)
And format that range of cells as Wingdings.
Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.
Hit the delete key on the keyboard to clear the cell.
If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")
The code will just check to see if that cell is empty.
Make Row 1 a little taller and put a button from the forms toolbar at the top of
that row.
Then assign this code to that button:
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim ListWks As Worksheet
Dim PrintWks As Worksheet
Dim testRng As Range
Dim TotalRng As Range
Set ListWks = ActiveSheet
Set PrintWks = Worksheets("sheet1")
With ListWks
Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With
For Each myCell In myRng.Cells
With myCell
If .Offset(0, -1).Value = "" Then
'do nothing, cell is not checked
Else
Set testRng = Nothing
On Error Resume Next
Set testRng = PrintWks.Range(myCell.Offset(0, -2).Value)
On Error GoTo 0
If testRng Is Nothing Then
MsgBox "Design error. Contact xxx at ####." & vbLf _
& "tell Frank that: " & .Offset(0, -2).Value _
& " didn't exist--on row: " & .Row
Exit Sub
End If
If TotalRng Is Nothing Then
Set TotalRng = testRng
Else
Set TotalRng = Union(TotalRng, testRng)
End If
End If
End With
Next myCell
If TotalRng Is Nothing Then
MsgBox "You didn't select any ranges to print!"
Else
TotalRng.PrintOut preview:=True
myRng.Offset(0, -1).ClearContents
End If
End Sub
ps. Names can't have spaces in them. I'd use Page001, page002, ...
Or even base it on their name:
Rudd_Frank_prt
And this line:
myRng.Offset(0, -1).ClearContents
clears out the checkmarks for the next time. You may not want this line.
