| Print Macro2.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Path | Filename | Sheet | |||
| 2 | C:\Path\ | Filename1.XLS | Sheet1 | |||
| 3 | C:\Path\ | Filename2.XLS | Sheet1 | |||
| 4 | C:\Path\ | Filename3.XLS | Sheet1 | |||
| 5 | C:\Path\ | Filename4.XLS | Sheet1 | |||
FileList | ||||||
Private Function FileExists(fname) As Boolean
FileExists = (Dir(fname)<> "")
End Function
Sub OpenPrint()
Dim i As Integer
Dim Filename_i As String
Dim TabName As String
Dim Filename_n As String
Dim EndNum As Integer
EndNum = Range("A1:A" & Range("A65536").End(xlUp).Row).Rows.Count
Range("A2").Select
'Confirm Filename
For i = 2 To EndNum
Filename_i = Range("A" & i).Value & Range("B" & i).Value
If FileExists(Filename_i) = False Then
MsgBox "The filename in row " & i & " on tab " & ActiveSheet.Name & " does not exist.", vbExclamation, "FILENAME ERROR"
GoTo ending
End If
Next i
'Format/Print
For n = 2 To EndNum
Filename_n = Range("A" & n).Value & Range("B" & n).Value
TabName = Range("C" & n).Value
Workbooks.Open Filename:=Filename_n, UpdateLinks:=0
Sheets(TabName).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Close
Next n
Range("A2").Select
ending:
End SubPrivate Function FileExists(fname) As Boolean
FileExists = (Dir(fname) <> "")
End Function
Sub OpenPrint()
Dim i As Integer
Dim Filename_i As String
Dim TabName As String
Dim Filename_n As String
Dim EndNum As Integer
EndNum = Range("A1:A" & Range("A65536").End(xlUp).Row).Rows.Count
Range("A2").Select
'Confirm Filename
For i = 2 To EndNum
Filename_i = Range("A" & i).Value & Range("B" & i).Value
If FileExists(Filename_i) = False Then
MsgBox "The filename in row " & i & " on tab " & ActiveSheet.Name & " does not exist.", vbExclamation, "FILENAME ERROR"
Exit Sub
End If
Next i
'Open & Print
For n = 2 To EndNum
On Error GoTo BadSheet
ReSheet:
Filename_n = Range("A" & n).Value & Range("B" & n).Value
TabName = Range("C" & n).Value
Workbooks.Open Filename:=Filename_n, UpdateLinks:=0
Sheets(TabName).Select
With ActiveSheet.PageSetup
.LeftFooter = ""
.CenterFooter = n - 1
.RightFooter = ""
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
If n = EndNum Then
Range("A2").Select
Exit Sub
End If
Next n
BadSheet:
ActiveWorkbook.Close
ThisWorkbook.Activate
NewSheetName = InputBox("The sheet listed in row " & n & " does not exist." & Chr(10) & Chr(10) & "Please enter a valid sheet name", "SHEET NAME ERROR", TabName)
Range("C" & n).Value = NewSheetName
Resume ReSheet
End Sub
