I have written the following PrintFiles() procedure to print 3 worksheets
from an external workbook. The code will look in column A for a list of
workbook files. There will be many workbook files (50+) with the same
worksheets to print. If it finds the file, then it opens the workbook and
prints the worksheets.

The code seems to work when I'm in debug mode, but not when I run it without
the debugger. It opens the workbook file and then stops. Any suggestions?

Public Sub PrintFiles()
'
' PrintFiles Macro
' Print files listed in Files tab
'
' Keyboard Shortcut: Ctrl+Shift+P
'
'--Updated: 2008-Aug-29

Dim intRow As Integer
Dim strFile As String

Range("A1").Select
intRow = 1
strFile = ActiveCell.Value
Do Until strFile = ""
'--Check for existence of file
If Dir(strFile) < "" Then
'--open workbook file
Workbooks.Open Filename:=strFile
'--print worksheet 1
ActiveWorkbook.Sheets("13 months").Select
ActiveWorkbook.ActiveSheet.PrintOut Copies:=1
'--print worksheet 2
ActiveWorkbook.Sheets("comp op stmt").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--print worksheet 3
ActiveWorkbook.Sheets("act v bud").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--close workbook
ActiveWorkbook.Close False
End If
ActiveCell.Offset(intRow, 0).Activate
strFile = ActiveCell.Value
Loop
Exit Sub
End Sub

Drop the Shift from the shortcut key combination and try it again.

TJ Dowling wrote:

I have written the following PrintFiles() procedure to print 3 worksheets
from an external workbook. The code will look in column A for a list of
workbook files. There will be many workbook files (50+) with the same
worksheets to print. If it finds the file, then it opens the workbook and
prints the worksheets.

The code seems to work when I'm in debug mode, but not when I run it without
the debugger. It opens the workbook file and then stops. Any suggestions?

Public Sub PrintFiles()
'
' PrintFiles Macro
' Print files listed in Files tab
'
' Keyboard Shortcut: Ctrl+Shift+P
'
'--Updated: 2008-Aug-29

Dim intRow As Integer
Dim strFile As String

Range("A1").Select
intRow = 1
strFile = ActiveCell.Value
Do Until strFile = ""
'--Check for existence of file
If Dir(strFile) < "" Then
'--open workbook file
Workbooks.Open Filename:=strFile
'--print worksheet 1
ActiveWorkbook.Sheets("13 months").Select
ActiveWorkbook.ActiveSheet.PrintOut Copies:=1
'--print worksheet 2
ActiveWorkbook.Sheets("comp op stmt").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--print worksheet 3
ActiveWorkbook.Sheets("act v bud").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--close workbook
ActiveWorkbook.Close False
End If
ActiveCell.Offset(intRow, 0).Activate
strFile = ActiveCell.Value
Loop
Exit Sub
End Sub

--

Dave Peterson

That worked! Thanks a bunch.

TJ


"Dave Peterson" wrote:

Drop the Shift from the shortcut key combination and try it again.

TJ Dowling wrote:

I have written the following PrintFiles() procedure to print 3 worksheets
from an external workbook. The code will look in column A for a list of
workbook files. There will be many workbook files (50+) with the same
worksheets to print. If it finds the file, then it opens the workbook and
prints the worksheets.

The code seems to work when I'm in debug mode, but not when I run it without
the debugger. It opens the workbook file and then stops. Any suggestions?

Public Sub PrintFiles()
'
' PrintFiles Macro
' Print files listed in Files tab
'
' Keyboard Shortcut: Ctrl+Shift+P
'
'--Updated: 2008-Aug-29

Dim intRow As Integer
Dim strFile As String

Range("A1").Select
intRow = 1
strFile = ActiveCell.Value
Do Until strFile = ""
'--Check for existence of file
If Dir(strFile) < "" Then
'--open workbook file
Workbooks.Open Filename:=strFile
'--print worksheet 1
ActiveWorkbook.Sheets("13 months").Select
ActiveWorkbook.ActiveSheet.PrintOut Copies:=1
'--print worksheet 2
ActiveWorkbook.Sheets("comp op stmt").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--print worksheet 3
ActiveWorkbook.Sheets("act v bud").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'--close workbook
ActiveWorkbook.Close False
End If
ActiveCell.Offset(intRow, 0).Activate
strFile = ActiveCell.Value
Loop
Exit Sub
End Sub

--

Dave Peterson