print excel worksheets to multiple pdf

Question

I found a macro that prints an excel worksheet to PDF, I want to be able to do this across multiple worksheets without having to use the worksheet name but with the worksheet index. I'd like to have the pdf saved based on a range in each worksheet (this range is the same on all sheets).

heres what i have:

Sub exceltoPDF1()
'Saves the excel print area to a PDF file

Dim fp As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Worksheets(1).Activate
Set rng = Worksheets(1).Range("A1")
'\\MNACPFS01\Home\sramdeo\Desktop\stuff
i = rng
fp = "\\MNACPFS01\Home\sramdeo\Desktop\stuff "
'fp = "H:\2013_FY148\07_OCT13\4. PROGRAM DOCS\APR\ " & "rng.pdf"""
Set wb = ActiveWorkbook
Set ws = Worksheets(1)

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=i, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True


End Sub

Answer 1

This will pass through all Worksheets contained in your Workbook wb using the index of the Worksheet:

Sub exceltoPDF1()
    'Saves the excel print area to a PDF file

    Dim fp As String
    Dim wb As Workbook
    Dim rng As Range

    Set wb = ActiveWorkbook

    Dim intWS As Integer
    For intWS = 1 To wb.Worksheets.Count
        With wb.Worksheets(intWS)
            Set rng = .Range("A1")
            '\\MNACPFS01\Home\sramdeo\Desktop\stuff
            i = rng
            fp = "\\MNACPFS01\Home\sramdeo\Desktop\stuff "
            'fp = "H:\2013_FY148\07_OCT13\4. PROGRAM DOCS\APR\ " & "rng.pdf"""

            .ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=i, _
                                    Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=False, _
                                    IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True
        End With
    Next intWS '<--- Change this from "i" to "intWS"

End Sub

Answer 2

Sub Cetak_Sampul()

Dim StartmRow As Integer
Dim EndmRow As Integer
Dim Msgm As String
Dim a As Integer

Sheets("Cvr").Activate
StartmRow = Range("RA")
EndmRow = Range("RW")

If StartmRow > EndmRow Then
    Msgm = "ERROR" & vbCrLf & "Record awal ga boleh lebih dari record akhir!"
    MsgBox Msgm, vbCritical, APPNAME
End If

For a = StartmRow To EndmRow
    Range("ON") = a
    If Range("PR") Then
        ActiveSheet.PrintPreview
    Else
        ActiveSheet.PrintOut
    End If
Next a

End Sub

(how to get the print as pdf, with the name of file save as in range ("ON") the file is here trim's