penfold255
penfold255

Reputation: 43

VBA nesting for each for PDF

I am having a problem in this output. It seems to mostly working but I am missing a step somewhere.

In the below code: K4 to K7 is number codes and l4 to l7 is some word names - 4 records Im trying to output this to PDF and it should only have 4 records however for some reason it outputs repeated versions, 16 in total. I know the loops are wrong. Anyway to fix this?

Sub foreachtest2()
Application.ScreenUpdating = False
Dim c As Range
Dim f As Range



Sheets("Lookup Table").Range("K4:K7").Name = "Rng"
Sheets("Lookup Table").Range("L4:L7").Name = "RngName"


For Each c In Range("Rng")
    For Each f In Range("RngName")

    Sheets("Site Report").Select
    Range("D7").Select
    ActiveCell.FormulaR1C1 = c.Value




Sheets("Site Report").Select
 Range("A1:M78").Select
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\path" & f.Value & c.Value & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False

Next
Next


  Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 48

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You dont need two loops, just one to iterate on the K column. At each iteration you retrieve the corresponding value in L by using ``Offset.

For Each c In Range("Rng")
    Set f  = c.Offset(, 1)
    ' ...
    ' do the work. I cant verify if the code inside your loop
    ' does the intended work. But this quick fix should solve the
    ' 16 values issue. Now you should have only 4 records to work on. 
Next

Upvotes: 3

Related Questions