CODEWITHSUNDEEP
vbaexcelpdf
Reed Rawlings
Reed Rawlings

Reputation: 91

Save As PDF while looping through cells

I'm trying to create VBA code that moves down a list, taking each of those cells and inputting their value to B2 (this value the name of a tutoring service). The value in B2 is then used by other cells to find reference values. I'd like each iteration of i (1 to 91) to save the new information as a PDF so that I can easily send those documents out.

My problem is that I cannot get the file to save, I receive Run-Time error 9 Subscript out of Range. The loop works exactly as I want.

How do I save as a PDF while looping through a range of values? 

Sub moveselection()
Dim i As Integer

For i = 1 To 91

Range("B2").Value = Range("H2").Offset(i, 0).Value
  ThisFile = Range("B2").Value
    Sheets("Parents").Ranges("A1:F16").ExportAsFixedFormat Type:=xlTypePDF,                           
Filename:= _
"H:\Projects\Nathan\ProviderPDF\ & ThisFile.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Next i

End Sub

Upvotes: 2

Views: 3561

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Couple of changes:

Sub moveselection()
Dim i As Integer

For i = 1 To 91

    Range("B2").Value = Range("H2").Offset(i, 0).Value
    ThisFile = Range("B2").Value

    Sheets("Parents").Range("A1:F16").ExportAsFixedFormat _
           Type:=xlTypePDF, _                         
           Filename:= "H:\Projects\Nathan\ProviderPDF\" & ThisFile & ".pdf", _
           Quality:= xlQualityStandard, IncludeDocProperties:=True, _
           IgnorePrintAreas:=False, OpenAfterPublish:=False

Next i

End Sub

If it still errors then please indicate which line gives the error

Upvotes: 2

Related Questions