Maybe you could incorporate some of this:

Option Explicit
Sub testme02()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Set wkbk = Workbooks.Open(myPath & myFiles(fCtr))
If WorksheetExists("report", wkbk) Then
wkbk.Worksheets("report").PrintOut preview:=True
Else
Beep
End If
wkbk.Close savechanges:=False
Next fCtr
End If

End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

(that last function is saved from a Chip Pearson post.)

jr2002 wrote:

Sub Printbooks()
DirName = InputBox("Enter the directory to search including final \
:", "Print Books")
Nextbook = Dir(DirName & "*.xls")
Do While (Nextbook < "" Or Nextbook < "G:\My
Documents\Report_printing_at_a_go\Printbooks.XLS")
MsgBox Nextbook & " is being opened"

Workbooks.Open DirName & Nextbook
For Each sheet In Workbooks()
On Error Resume Next
If Worksheets("Report").Activate < "" Then
' Application.Dialogs(xlDialogPrinterSetup).Show
If ActiveSheet.Name = "Report" Then ActiveSheet.PrintOut
preview:=False, Copies:=1
'ActiveWorkbook.PrintOut
ActiveSheet.Close
End If
Next sheet

MsgBox Nextbook & " is being closed"
ActiveWorkbook.Close
Nextbook = Dir()
Loop

End Sub

The above code intend to print every sheet named report from the
workbooks in a particular directory. Why does it print them twice?
Could anyone help me?

jr2002

--
jr2002
------------------------------------------------------------------------
jr2002's Profile: http://www.excelforum.com/member.php...o&userid=15447
View this thread: http://www.excelforum.com/showthread...hreadid=270426

--

Dave Peterson