Lookup relative to sheet name

Hello!

I am writing a module to get excel to use VLOOKUP to get data relative to the name of a current sheet. Please see code. Can you advise why it comes up with an error? (Is it not possible to attach files in questions)?

It is the line shtName = ActiveSheet.Name
that it doesnt like. By the way, other aspects of writing this routine have gone quite well!

Option Explicit

Option Compare Text

Dim intSheetCount As Integer
Dim shtSheet As Worksheet
Dim shtName As String
Dim numRows As Integer

Sub post()

Sheets("FLAT A").Select

For intSheetCount = 1 To 3

shtName = ActiveSheet.Name

numRows = Range("a33").CurrentRegion.Rows.Count

Sheets(intSheetCount + 9).Cells(numRows + 33, 1).Value = Sheets("demands data").Range("a1").Cells(2, 2).Value
Sheets(intSheetCount + 9).Cells(numRows + 33, 2).Value = Sheets("demands data").Range("a1").Cells(3, 2).Value
Sheets(intSheetCount + 9).Cells(numRows + 33, 4).Value = "=VLOOKUP(""shtName"",input,2)"


Next intSheetCount

End Sub