Why my VBA Code Prints each sheet twice

[jr2002]

Sub Printbooks()
DirName = InputBox("Enter the directory to search including final
:", "Print Books")
Nextbook = Dir(DirName & "*.xls")
Do While (Nextbook <> "" Or Nextbook <> "G:\M
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.PrintOu
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 th
workbooks in a particular directory. Why does it print them twice
Could anyone help me?


jr200

 

[Dave Peterson]

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.)