excel vba - How to assign value to rows according to sheet order in VBA -

since i'm new vba , got issue order of sheet name not in place expected. say: in workbook have sheet1 cell a7= name of sheet1 a8= name of sheet2, a9= name of sheet3. beside sheet1, 2, 3 have other sheets on workbook

i write loop fill value of sheet name sheet 1 @ b7,b8,b9 sheet().name function, thing if change order of sheets moving them, order of cell a1, a2, a2 not matching sheet name anymore

please fix code make order of sheet name in right place accordingly

private sub worksheet_activate()  dim lrow long dim i, j integer dim sh worksheet dim result string dim shname string dim sname long  sheets("sheet1").range("b7:b9").clear = 1 sheets.count     thisworkbook        set sh = .sheets(i)        shname = .sheets(i).name   '     msgbox .sheets(i).name     end     sh         lrow = .range("r" & .rows.count).end(xlup).row     end     select case shname         case shname            '7 starting row @ sheet 1            j = 7 lrow             if sheets(shname).cells(j, 1).value = shname(j)                 worksheet.sheet("sheet1")cells(j,2).value ="something here"            endif          next j     end select end sub 

you need use find function locate worksheet name range on worksheet1 , can put row count next it.

try this:-

private sub worksheet_activate()    dim lrow long   dim i, j integer   dim sh worksheet   dim result string   dim shname string   dim sname long   dim rng range    sheets("sheet1").range("b7:b9").clear    = 1 sheets.count       thisworkbook          set sh = .sheets(i)          shname = .sheets(i).name     '     msgbox .sheets(i).name       end       sh           lrow = .range("r" & .rows.count).end(xlup).row       end        set rng = range("a7:a9").find(shname)        'only write out if sheet name listed       if not rng nothing         rng.offset(0, 1).value = lrow       end if   next  end sub