Hi, courtconn!
You can check DataValList and the ValueCell dynamic names from the Formula tab, Names or Defined Names group (I don't remember exactly how it is in the English version), Name Manager icon. You should, you must find them... if not, we're in serious trouble.
The code isn't printing anything, that's to say, it doesn't output to a printer device either local or network. It just that I missed your 2nd. point in your original requirement.
Download again the file from same previous link.
Worksheet changes:
1) Addition of column B to mark with "X" selectable reports to be printed
2) New named ranges:
a) PrintCell: cell with data validation list with entries select All/Selected
b) PrintList: print list associated to DataValList (dynamic)
3) cell D1: total of reports to be printed
=SI(PrintCell="All";FILAS(DataValList);CONTAR.SI(B:B;"X")) -----> in english: =IF(PrintCell="All",ROWS(DataValList),COUNTIF(B:B,"X"))
Code updated:
Code:
Option Explicit
Sub MarvelAgentsOfShield()
'
' constants
' ranges
Const ksWSSource = "Hoja1"
Const ksWSTarget = "Hoja1"
Const ksValue = "ValueCell"
Const ksDataVal = "DataValList"
Const ksPrint = "PrintCell"
' print
Const ksPrintAll = "All"
Const ksPrintSelected = "Selected"
Const ksX = "X"
' path
Const ksFolder = ""
' format
Const ksSeparator = " - "
Const ksAddedText = ""
'
' declarations
Dim rngV As Range, rngDV As Range, rngP As Range
Dim sPath As String, bOk As Boolean, sActivePrinter As String
Dim I As Long, A As String
'
' start
' ranges
Set rngV = Worksheets(ksWSSource).Range(ksValue)
Set rngDV = Worksheets(ksWSSource).Range(ksDataVal)
Set rngP = Worksheets(ksWSSource).Range(ksPrint)
' path
If ksFolder = "" Then
sPath = ActiveWorkbook.Path
Else
sPath = ksFolder
End If
sPath = sPath & Application.PathSeparator
' print
sActivePrinter = Application.ActivePrinter
Application.Dialogs(xlDialogPrinterSetup).Show
'
' process
With rngDV
For I = 1 To .Rows.Count
' id
A = .Cells(I, 1).Value
rngV.Value = A
' added text
If ksAddedText <> "" Then
A = A & ksSeparator & ksAddedText
Else
A = A & ksSeparator & Format(Now(), "mm.dd.yy")
End If
' pdf
Worksheets(ksWSTarget).ExportAsFixedFormat xlTypePDF, sPath & A
' print
Select Case rngP.Value
Case ksPrintSelected
If UCase(.Cells(I, 2).Value) = ksX Then
bOk = True
Else
bOk = False
End If
Case ksPrintAll
bOk = True
Case Else
bOk = False
End Select
If bOk Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End If
Next I
End With
'
' end
' print
Application.ActivePrinter = sActivePrinter
' ranges
Set rngP = Nothing
Set rngDV = Nothing
Set rngV = Nothing
' ring
Beep
'
End Sub
Hope I didn't miss anything now.
Regarding printing on a network device instead of on a local one, you can do it in 2 ways:
- the easy and lazy (and I'd choose it so I work less; me, not you!)
- the hard but smarter (this will cost you extra)
Excel prints by default to your defined or -worth repeating- default printer, so:
- the 1st, is setting your printer to the network device desired and after doing the job setting it again to the previous one... and I shouldn't have worked any more
- the 2nd, ... do you still want to make me work more? it's cruel... was adding these lines (yet done) at the end of th start section of the code:
Code:
' print
sActivePrinter = Application.ActivePrinter
Application.Dialogs(xlDialogPrinterSetup).Show
and these at the start of the end section of the code:
Code:
' print
Application.ActivePrinter = sActivePrinter
Regards!
