You will need some error checking in case the sheets don't actually exist in the workbook.
it looks like you are looping through column B that has a list of sheet names and want range N18 to display next to it.
Something like

Sub Button1_Click()
    Dim Lstrw As Long, rng As Range, c As Range
    Dim Name As String

    Lstrw = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Range("B1:B" & Lstrw)

    For Each c In rng.Cells
        Name = c
        c.Offset(, 1) = "='" & Name & "'!N18"
    Next c

End Sub

Or you can just list the sheets and show N18 next to it, run this code in a Sheet named "Sheet1"

Sub GetTheSh()
    Dim sh As Worksheet, ws As Worksheet

    Set ws = Sheets("Sheet1")

    For Each sh In Sheets

        If sh.Name <> ws.Name Then

            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1) = sh.Name
            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(0, 1) = sh.Range("N18")

        End If

    Next sh

End Sub

Thank you to everyone for your help! I actually found that I had just made a silly error: the line Do While colvar<=26 should have been Do While colvar<26 . The cells were being filled, but the error manifested because one cell was being filled by a nonexistent object.
I did decide to use the .Formula modifier rather than .Value . Thank you to Jeeped for suggesting that.