编译错误无效下一个控制变量参考VBA

Question

我继续获得编译错误无效的下一个控制变量参考，任何人都可以提供帮助。

我需要代码来循环通过Sheep1列A并将值复制并粘贴到Sheep2（R1），然后循环通过Sheep1列B，然后将每个值粘贴到Sheet2（i7），然后将工作表保存为新的PDF文档

 Private Sub CommandButton1_Click()
Dim i As Long
Dim n As Long
Dim m As Integer
NumRows1 = Range("A2", Range("A2").End(xlDown)).Rows.Count
NumRows2 = Range("B2", Range("B2").End(xlDown)).Rows.Count
For i = 2 To NumRows1
    Range("i").Select
    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("R1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
For n = 2 To NumRows2
Range("n").Select
    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("I7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
For m = 0 To 300
Sheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "" & CStr(m) & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=True, _
        OpenAfterPublish:=False
Next i
 Next n
   Next m
 Application.ScreenUpdating = True
End Sub

Answer 1

尝试此

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim lastRow As Long
    Dim cel As Range, rng As Range
    Set srcSht = ThisWorkbook.Sheets("Sheet1")  'this is your source sheet
    Set destSht = ThisWorkbook.Sheets("Sheet2") 'this is your destination sheet
    With srcSht
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'get last row with data in Column A of srcSht
        For Each cel In .Range("A2:A" & lastRow)            'loop through each cell in Column A of srcSht
            cel.Copy destSht.Range("R1")                    'copy cell in Column A of srcSht to Cell R1 of destSht
            cel.Offset(0, 1).Copy destSht.Range("I7")       'copy cell in Column B of srcSht to Cell I7 of destSht
            Set rng = Union(destSht.Range("R1"), destSht.Range("I7"))   'union cell R1 and I7
            With rng.Font               'format union range
                .Name = "Calibri"
                .Size = 20
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .ThemeFont = xlThemeFontMinor
            End With
            destSht.Range("I7").Font.Size = 16
            'I've not tested save as pdf file part
            destSht.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=ThisWorkbook.Path & "" & (cel.Row - 1) & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                OpenAfterPublish:=False
        Next cel
    End With
End Sub

注意：我尚未测试保存文件作为pdf part。