in VBA sheet is sheet8

how can this appear in =index('sheetname'! c,(row()-1)*8+2)


macro is currently selecting sheets name as appears on the tab but this
changes every day. I need to use vba identity within this formula for it to
work.


James