excel - 修改当前代码循环到下一行

Question

我正在尝试创建一个宏，它将第一行 A8:V8 从 [Summary] 选项卡复制到 [Annual Statement] 选项卡，重新计算，然后将 [Annual Statement] 选项卡保存为 pdf，其中单元格 A8 的名称来自 [摘要] 选项卡。此代码按原样用于第一行 A8:V8。我想让这个宏更加动态并循环到下一行，A9:V9，然后重复将值复制到 [Annual Statement] 选项卡并保存为 pdf 的相同过程，然后再次重复整个过程下一行。
这是代码 -

Sub AnnualStatements()

Dim RI As Workbook
Set RI = ThisWorkbook

Dim strpath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String


**'Copies over policy information from summary to annual statement tab**

Worksheets("Summary").Range("A8:V8").Copy Worksheets("Annual Statement").Range("O3")

Calculate

**'Below is what I want to change the above line to but it isn't working**

For i = 1 to 10

Worksheets("Summary").Range(Cells(7+i, 1), Cells(7+i, 21)).Copy Worksheets("Annual Statement").Range("O3")

Calculate

**Creates location and path to save annual statement pdf file to**
strpath = "C:Users\Documents"
strName = Sheets("Summary").Range("A8")

**‘strName = Sheets(“Summary”).Cells(7+i,1)**  '' tried changing to this but not working

strFile = strName & "_Annual Statement" & ".pdf"
strPathFile = strpath & strFile

**Saves as pdf**

Worksheets("Annual Statement").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

**'Next i 'tried adding this but not working** 

End Sub

Answer 1

此代码将循环直到在 Summary 的 A 列中找到一个空白单元格。工作表。

Option Explicit

Sub AnnualStatements()
Dim RI As Workbook
Dim rngData As Range
Dim strpath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String

    Set RI = ThisWorkbook

    strpath = "C:Users\Documents\"

    Set rngData = RI.Worksheets("Summary").Range("A8:V8")

    Do
        'Copies over policy information from summary to annual statement tab**
        rngData.Copy RI.Worksheets("Annual Statement").Range("O3")

        Calculate

        ' Create filename for PDF

        strName = rngData.Cells(1,1).Value

        strFile = strName & "_Annual Statement" & ".pdf"

        strPathFile = strpath & strFile

        ' Saves as pdf**

        RI.Worksheets("Annual Statement").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strPathFile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
                
        Set rngData = rngData.Offset(1)

    Loop Until rngData.Cells(1, 1).Value = ""
    
End Sub