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, Sep 23 '17 at 09:30
Whoa... that's some pretty interesting indenting you've got going on there ;-) What line of code is the compile error on? — SlowLearner, Sep 23 '17 at 09:39
You have to close your nested for-loops in oposite order of creation. First `Next m` then `Next n`and `Next i`at 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, Sep 23 '17 at 09:40
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 — AbdA, Sep 23 '17 at 09:41
@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, Sep 23 '17 at 09:43
@Mrig I want to copy A2 to R1 Then B2 to i7 Then save the sheet as PDF then A3 to R1 Then B3 to i7 then save the sheet as a PDF and so on. There are 289 rows. — AbdA, Sep 23 '17 at 09:46

Mrig · Accepted Answer · 2017-09-23T10:15:22.303

3

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.

edited Sep 23 '17 at 10:15

answered Sep 23 '17 at 09:57

Mrig

11,612
2
13
27

@AbdullahAlbyati - Great! – Mrig Sep 23 '17 at 10:05
Nice, just replace `ActiveSheet.ExportAsFixedFormat` with `destSht .ExportAsFixedFormat`. You made the world better ;) – BitAccesser Sep 23 '17 at 10:05
@BitAccesser - Good catch there. – Mrig Sep 23 '17 at 10:06
@AbdullahAlbyati - In the above code I've not worked on saving sheet as pdf, so kindly make changes as required. – Mrig Sep 23 '17 at 10:07
@Mrig Thanks Again – AbdA Sep 23 '17 at 10:08
1

And, yes, my attempted contribution was somewhat rushed... as per @BitAccesser suggestion please also see this for avoiding the use of select: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SlowLearner Sep 23 '17 at 10:14
@SlowLearner - I guess I've not used `.Select` anywhere in my code. – Mrig Sep 23 '17 at 10:21
@Mrig no, it is used in the OP – SlowLearner Sep 23 '17 at 10:26
@SlowLearner - Alright! To address OP directly, you can include @ followed by OP name in comments just as you addressed me in your previous comment. – Mrig Sep 23 '17 at 10:32
@AbdullahAlbyati in case you missed it, we thought you might like to know about some ways for avoiding the use of select, pls see this link: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Hope it helps :) Mrig I had assumed that OP would be notified automagically... (but now I am unsure who the post owneris) oops – SlowLearner Sep 23 '17 at 10:36
@SlowLearner - No problem at all! See [this](https://meta.stackexchange.com/a/125212) for details on comment notification. – Mrig Sep 23 '17 at 10:41

Compile error invalid Next Control Variable Reference VBA

1 Answers1