Compile error invalid Next Control Variable Reference VBA

Question

I keep getting Compile error invalid Next Control Variable Reference Anyone can help.

I need the code to loop through Sheet1 Column A and copy and paste the value to Sheet2(R1) Then loop through Sheet1 column B and copy each value paste it to Sheet2(I7) then save the worksheet as a new PDF document

 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

What are you exactly trying to do with For loop i? Are you copying Column A from Sheet1 to Column R of Sheet2 and same for Column B to Column I starting form I7? — Mrig
Whoa... that's some pretty interesting indenting you've got going on there ;-) What line of code is the compile error on? — SlowLearner
You have to close your nested for-loops in oposite order of creation. First Next m then Next nand Next iat last. You can't close a loop while another one is running inside. You are aware that the number of iteration is the multiplication of the three loop counts (e.g Numrows1 = Numrows2 = 100, there are 100*100*300 = 3000000 iterations!). — BitAccesser
I want to copy one row at a time from Column A from Sheet1 to Column R Cell 1 in Sheet2 and same for Column B to Column I Cell 7 in Sheet 2 — Abdullah Albyati
@AbdullahAlbyati - After copying A1 to R1 do you also want to copy A2 to R2 then A3 to R3 and so on..... Or simply, do you want to copy A1:A10 to R1:R10 if 10 is the last row. — Mrig

Mrig Mrig · Accepted Answer · 2017-09-23T09:57:39

Try this

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

Note : I've not tested saving file as pdf part.

Compile error invalid Next Control Variable Reference VBA

1 Answers