Save sheet in workbook as pdf using loop

Question

I have a workbook with the following named sheets: Input, Presentation, Results.

The Results sheet has rows of information that is shown in Presentation sheet via cell B2 in the Input sheet. B2 starts with the value 1, that looks up range A5:A5000 in Results sheet. Changing cell B2 in Input changes the Presentation sheet.

I'm looking for a code for a macro that will take the value in cell B2 of "Input" sheet, save the "Presentation" sheet as a PDF by name of what is in cell B4 of "Input" sheet, add 1 to the value in cell B2 of "Input" sheet and loops until if finds an empty cell in range A5:A5000 in "Results" sheet.

I want the files saved one by one in the same folder.

This is my code:

Sub ProjectionStatementRecord()

' ProjectionStatementRecord Macro

Sheets("Results sheet").Activate
   Range("A4").Select

   Do While True
   If Selection.Value = "" Then
   Exit Do
   Else
    Sheets("Input sheet").Activate
    Range("B3").Select
    Selection.Copy
    Sheets("Presentation").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Kristine\Desktop\Retirement Projection\" & Selection.Text & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    Sheets("Results sheet").Activate
    Selection.Offset(1, 0).Select
    End If
    Loop
End Sub

I am getting an error that says "file not saved"

Please help.

You may want to see [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). `I'm very new to VBA so I don't have a code to show.` Being new to VBA is no excuse :) You can spend some time learning the language. And once you have tried some code you can then post the code sharing **1.** What were you expecting? **2.** What is not working? Include Error message if applicable **Note:** If this is urgent and you do not have the time nor want to learn the language then I would recommend posting this on Freelancer.com or a similar site. — Siddharth Rout, Jan 28 '20 at 06:03
Hi. I have added my code and the error I am getting. I also looked at similar posts but the answers are not helping me. — Tangeni-Omwene, Jan 28 '20 at 08:22
Ok. Now look at [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Avoid the use of `.Select/.Activate` Work with objects — Siddharth Rout, Jan 28 '20 at 08:37
Also instead of `Do While True` use a For Loop. Find the last row in that column as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then use `For i = 2 to LastRow` to loop — Siddharth Rout, Jan 28 '20 at 08:38

Save sheet in workbook as pdf using loop

0 Answers0