How can I remove ws.name
I tried to create a PDF file using following script, it works but when I remove
ws.Name, the script breaks.
Sub createPDFfiles() Dim ws As Worksheet Dim strName As String For Each ws In ActiveWorkbook.Worksheets On Error Resume Next 'Continue if an error occurs ' Name PDF files based on the worksheet Index (e.g Annex 1.1.1, Annex 1.1.2, etc.) strName = Range("A10").Text & " " & Range("C7").Text & ws.Name ' If you want to name the PDF files differently just change the Fname variable above to ' whatever you like. For example if you changed Fname to: ' ' Fname = "C:\myFolder\pdfs\" & ActiveWorkbook.Name & "-" & ws.Name ' ' The files would be stored in C:\myFolder\pdfs, and named using the ' spreadsheet file name and the worksheet name. ' ' WARNING: Using worksheet names may cause errors if the names contain characters that Windows ' does not accept in file names. See below for a list of characters that you need to avoid. ' ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=strName, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Next ws End Sub
How can I remove this
ws.Name and still work?
Rangeobjects are unqualified in this statement:
strName = Range("A10").Text & " " & Range("C7").Text & ws.Name
So they are referring to those cells on the active sheet, rather than on sheet
If you wanted to refer to those cells on each worksheet you loop through, then qualify them appropriately:
strName = ws.Range("A10").Text & " " & ws.Range("C7").Text
You may wish to test that strName is not null before you try to save the PDF.
I'd also recommend losing the
On Error Resume Nextline, and handling errors properly.