Printing all the sheets in the excel to PDF

[gunjan8882]

Dear all,

I am naive to excel macros. I am trying to build a macro in order to select and print all the sheets in the workbook to PDF. And then do the same for the rest of the files in the folder. However, I am getting error on the below line. Please help to fix the same. Thanks a lot in advance.

Error line: ActiveWorkbook.Worksheets.Select

-------------------------------------------------------------------------------------------------
Private Sub CommandButton7_Click()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("D:\Work\Macro1")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
ActiveWorkbook.Worksheets.Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
ActiveWorkbook.Close
End If
Next file
Application.CutCopyMode = False
Set oFSO = Nothing
End Sub
----------------------------------------------------------------------------------------------

 

[gunjan8882]

Can anybody please help me with the above query. Thanks in advance.

 

[yky]

Which sheet you want to select? You need to tell Excel which sheet to select. Since you want to loop through all sheets, maybe you want to try (code note tested):

for i = 1 to ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).ExportAsFixedFormat Type:=xlTypePDF

Next i