sadiablo
10-30-2013, 05:46 PM
I am trying to write a Macro to print certain areas of multipal docs. I have it working as expexted from a module but when I try to put the same code into "Private Sub Workbook_Open()" so it will run when I open the document, it prints the entire documents not the selected parts. What am I missing?
I have high lighted the area that seems to be not working in orange.
The code I am using is,
Private Sub Workbook_Open()
Set WB = Workbooks.Open("C:\Folder\subfolder\book1.xls")
WB.PrintOut , Copies:=2, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book2.xls")
WB.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book3.xls")
Range("A39:Q76").Select
ActiveSheet.PageSetup.PrintArea = "A38:Q75"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book4.xls")
Range("A40:P78").Select
ActiveSheet.PageSetup.PrintArea = "A40:P78"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Nothing
With CreateObject("Word.Application")
With .Documents.Open("C:\Folder\subfolder\doc1.doc")
.PrintOut
.Close False
End With
.Quit
End With
With CreateObject("Word.Application")
With .Documents.Open("C:\Folder\subfolder\doc2.doc")
.PrintOut
.Close False
End With
.Quit
End With
End Sub
Any help would be very much appeciated.
I have high lighted the area that seems to be not working in orange.
The code I am using is,
Private Sub Workbook_Open()
Set WB = Workbooks.Open("C:\Folder\subfolder\book1.xls")
WB.PrintOut , Copies:=2, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book2.xls")
WB.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book3.xls")
Range("A39:Q76").Select
ActiveSheet.PageSetup.PrintArea = "A38:Q75"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Workbooks.Open("C:\Folder\subfolder\book4.xls")
Range("A40:P78").Select
ActiveSheet.PageSetup.PrintArea = "A40:P78"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
WB.Close SaveChanges:=False
Set WB = Nothing
With CreateObject("Word.Application")
With .Documents.Open("C:\Folder\subfolder\doc1.doc")
.PrintOut
.Close False
End With
.Quit
End With
With CreateObject("Word.Application")
With .Documents.Open("C:\Folder\subfolder\doc2.doc")
.PrintOut
.Close False
End With
.Quit
End With
End Sub
Any help would be very much appeciated.