Macro run until empty (variable cell)

[acurle]

Hello,

Hoping someone could help?
I'm just getting back into VBA and although I've managed to get quite far, I'm drawing a mind blank getting the end at variable table length.
I have a table (called "Mass_Doc_Run") where the length can be variable Sheets("Mass Doc Run"), columns A, B and C have data which is copy pasted into another sheet. There is then a corresponding IF statement depending upon the answer given within a cell to create a PDF and save down.
Can someone help with repeating the above until the table "Mass_Doc_Run" ends? This is variable length which is the bit which is causing me a problem.

Hope this makes sense - first time using a forum!

Thanks,
Alex

Sub TEST()

    Sheets("Mass Doc Run").Select
    Range("A2").Select
    Selection.Copy
   
    Sheets("Template Control").Select
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("Mass Doc Run").Select
    Range("B2").Select
    Selection.Copy
   
    Sheets("Template Control").Select
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    Sheets("Mass Doc Run").Select
    Range("C2").Select
    Selection.Copy
   
    Sheets("Template Control").Select
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    If Sheets("Template Control").Range("B9").Value = "POC" Then
   
        Sheets("Proof of Compliance (POC)").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Sheets("Template Control").Range("B1").Text, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
       
    ElseIf Sheets("Template Control").Range("B9").Value = "POS" Then
   
        Sheets("Proof of Sustainability (POS)").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Sheets("Template Control").Range("B1").Text, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
       
    End If
   
       
    
End Sub

 

[myall_blues]

Hi Alex

I hope this is what you meant.

Sub TEST()
    Dim lastRow As Long, i As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        Sheets("Template Control").Range("B6").Value = Sheets("Mass Doc Run").Range("A" & i).Value2
        Sheets("Template Control").Range("B8").Value = Sheets("Mass Doc Run").Range("B" & i).Value2
        Sheets("Template Control").Range("B9").Value = Sheets("Mass Doc Run").Range("C" & i).Value2
        
        If Sheets("Template Control").Range("B9").Value = "POC" Then
            Sheets("Proof of Compliance (POC)").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            Sheets("Template Control").Range("B1").Text, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next i
End Sub