VBA code that loops through not all sheets

Question

Kindly requesting help with my VBA code. I have a mastersheet that contains a table summarizing several projects where each row contains information about one project. I then have tabs for each project and the code is to pull information from the master sheet into the corresponding tab. To do this, I have listed the project names on the first row of the table in the master tracker and used the same name for the tab. As the code moves from sheet to sheet, the code reads the sheet name and then uses the vlookup to pull the information from the master tracker into the corresponding sheet.

I am getting an error stating on my 'next' command stating I have a 'next with for'. Please help. I have intermittent experience with VBA - not frequent enough to be proficient.

Sub Macro1()

'Run the API for every sheet in the workbook
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets

    'Look to see what the sheet is named and run the macro if it is not what is below
    If Sht.Name <> "Modified" And Sht.Name <> "Original" And Sht.Name <> "Master Tracker" Then
        Sht.Activate

'Looks for the tab name in the master sheet and copies the information from the corresponding row into the project tab


    Range("C2:N2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,2,FALSE)"

    Range("C3:N3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,3,FALSE)"

    Range("A9:D24").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,4,FALSE)"

    Range("F9:J24").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,5,FALSE)"

    Range("L9:N15").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,6,FALSE)"

    Range("L17:N24").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,7,FALSE)"

    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,8,FALSE)"

    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,9,FALSE)"

    Range("F4:I6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,10,FALSE)"

    Range("K4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,11,FALSE)"

    Range("K6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,12,FALSE)"

    Range("N4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,13,FALSE)"

    Range("N6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,14,FALSE)"


Next

'activate the worksheet that was originally active
starting_ws.Activate


End Sub

'At the end of the program say it has all been updated

MsgBox ("Update Complete

Welcome to SO. You are msising 1 `End If` in your code. It should be placed before your `Next` line. — Foxfire And Burns And Burns, Sep 17 '18 at 08:33
Thanks much to both of you! The End If fixed the issue. I have also moved the End Sub after the MsgBox command. — Patti, Sep 17 '18 at 09:01
You also don't need to use `Range(??).Select` and then `ActiveCell.FormulaR1C1 =` - you can just use `Range(??).FormulaR1C1 =`. Not using `Select` tends to reduce or prevent a *lot* of issues later on — Chronocidal, Sep 17 '18 at 09:08

score 0 · Answer 1 · answered Sep 17 '18 at 11:22

Welcome to SO. Let's try to fix your code a bit:

Your main problem was the missing End if. Everytime you code, please note that some instructions have a start point and a end point. If you don't respect the order, your code will fail. This happens to some instructions like For..Next, Do..Loop,If...End if and so on. I suggest you to google for some of them because there are millions of websites with help, like:

If...Then...Else Statement (Visual Basic)

Now, about your code, just an advice. Try to avoid using Select, because it consumes time . Most of properties and methods of Range object can be invoked without selecting them. Just an example:

Sub CODE_WITH_SELECT()
Range("B4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,8,FALSE)"
End Sub

Sub CODE_WITHOUT_SELECT()
Range("B4").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,8,FALSE)"
End Sub

Both subs will do exactly the same, but second one is faster.

Please, check How to avoid using Select in Excel VBA

Now, if we apply this to your code, probably I would use:

Sub Macro1()

Application.ScreenUpdating = False 'this will turn off screen updating, so code will execute faster

'Run the API for every sheet in the workbook
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets

    'Look to see what the sheet is named and run the macro if it is not what is below
    If Sht.Name <> "Modified" And Sht.Name <> "Original" And Sht.Name <> "Master Tracker" Then
        Sht.Activate

        'Looks for the tab name in the master sheet and copies the information from the corresponding row into the project tab
        Range("C2:N2").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,2,FALSE)"

        Range("C3:N3").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,3,FALSE)"

        Range("A9:D24").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,4,FALSE)"

        Range("F9:J24").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,5,FALSE)"

        Range("L9:N15").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,6,FALSE)"

        Range("L17:N24").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,7,FALSE)"

        Range("B4").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,8,FALSE)"

        Range("B5").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,9,FALSE)"

        Range("F4:I6").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,10,FALSE)"

        Range("K4").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,11,FALSE)"

        Range("K6").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,12,FALSE)"

        Range("N4").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,13,FALSE)"

        Range("N6").FormulaR1C1 = "=VLOOKUP(MID(CELL(""filename"",R[-2]C[-15]),FIND(""]"",CELL(""filename"",R[-2]C[-15]))+1,255),'Master Tracker'!R2C1:R35C14,14,FALSE)"
    End If
Next Sht

'activate the worksheet that was originally active
starting_ws.Activate

'At the end of the program say it has all been updated
MsgBox "Update Complete"

Application.ScreenUpdating = True 'we activate again the screen updating
End Sub

I added Application.ScreenUpdating = False for more speed.

Application.ScreenUpdating Property (Excel)

Test it and let us know your thoughts! :)

VBA code that loops through not all sheets

1 Answers1