CODEWITHSUNDEEP
vbaexcelpdf
Solar Mike
Solar Mike

Reputation: 8415

mac excel vba loop : from list & then export as pdf

So lost myself in this: I have a list on one sheet (studentlist) that has 160 student numbers. Would like to paste each student number in cell A1 ion the Feedback sheet and then export as pdf to a file with the student number as the filename. Got this far... Cheers Mike

Sub Pdfexportmacro()

Dim rCell As Range
Dim rRng As Range
Dim SNum As Integer

'Student numbers in cells A7:A160, set to A7:A9 for testing
Sheets("studentlist").Activate
Set rRng = Range("A7:A9")

For Each rCell In rRng.Cells
     SNum = rCell.Value

     ' Write student number to cell A1 on Feedback sheet:
        Sheets("Feedback").Activate
        Range(“A1”).Activate
        ActiveCell.Value = SNum

       ' Export & save file as pdf using SNum as filename:
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Macintosh HD:Users:Michael:Desktop:" & rCell.Value, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Next rCell

End Sub

Upvotes: 1

Views: 1550

Answers (1)

user3598756
user3598756

Reputation: 29421

I'm not a MAC user so I may be missing some restrictions I don't have in Windows OS, but you may be after something like follows:

Option Explicit

Sub Pdfexportmacro()

    Dim rCell As Range, rRng As Range

    'Student numbers in cells A7:A160
    Set rRng = Worksheets("studentlist").Range("A7:A160") '<--| set your "students" range

    With Worksheets("Feedback") '<--| reference "Feedback" worksheet
        For Each rCell In rRng '<--| loop through "students" range
            .Range("A1").Value = rCell.Value '<--| write current student number to cell A1 on Feedback sheet

           ' Export & save file as pdf using SNum as filename:
            .ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
            "Macintosh HD:Users:Michael:Desktop:" & rCell.Value, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        Next rCell
    End With

End Sub

Upvotes: 1

Related Questions