Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why my VBA Code Prints each sheet twice
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 -- jr200 ----------------------------------------------------------------------- jr2002's Profile: http://www.excelforum.com/member.php...fo&userid=1544 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why my VBA Code Prints each sheet twice
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserted Shape Prints in Different Location on Sheet | Excel Discussion (Misc queries) | |||
Excel 2007 spread sheet prints slow. | Excel Discussion (Misc queries) | |||
Only part of my excel sheet prints to a pdf | Excel Discussion (Misc queries) | |||
Excel Sheet Only Prints a Line | Excel Discussion (Misc queries) | |||
How to +1 to a cell everytime the sheet prints? | Excel Programming |