Printing the same range in a workbook

[Guest]

How can I use macro to print the same range for all the workbooks in a
specific folder.

Thanks.

 

[Bernie Deitrick]

nc,

Pick one, change the path and the print area range address:

Sub PrintOneSheetFromAllFilesInFolder()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$12"
ActiveSheet.PrintOut
ActiveWorkbook.Close True
Next i
End If
End With
End Sub

Sub PrintAllSheetsFromAllFilesInFolder()
Dim mySht As Worksheet
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
For Each mySht In ActiveWorkbook.Worksheets
mySht.PageSetup.PrintArea = "$A$1:$D$12"
mySht.PrintOut
Next mySht
ActiveWorkbook.Close True
Next i
End If
End With
End Sub

 

[Ron de Bruin]

Hi

You can try this macro for all files in the folder C:\Data
It will print A1:C1 from the first sheet of every workbook

See this line


Sub Example1()
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)

mybook.Worksheets(1).Range("A1:C1").PrintOut

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub