VB code to place number sequence across visible sheets

[Jriley_11]

Hello everyone,
I have a workbook with around 50 sheets or so. I have code written to hide/unhide sheets based on userform inputs. I am stuck trying to write the code for excel to number the visible pages 1, 2, 3...etc. in cells A9 in each sheet. The visible sheets change and are different for each task at hand. Does anyone know how I would write this? I would think it would be a loop and if visible logic but cannot get code to work right for me.

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

I have code written to hide/unhide sheets based on userform inputs.... The visible sheets change and are different for each task at hand.

Does the code need to also clear A9 on the hidden sheets as they may contain a page number from a previous scenario? Or is it sufficient to just have the correct page numbers in the visible sheets?

 

[Jriley_11]

I just need code to number the visible sheets and skip the hidden sheet. Example: sheet1, sheet2 and sheet4 and visible. Sheet3 hidden. Code needs to write cell a9.value=1 on sheet1, =2 on sheet2, =3 on sheet4. Nothing on sheet 3. And also the hidden/visible sheets change based on inputs so code would need to number only based on visible condition. Thanks!

 

[Peter_SSs]

OK, give this a try

Sub Number_Sheets()
  Dim i As Long, k As Long
  
  For i = 1 To Sheets.Count
    If Sheets(i).Visible Then
      k = k + 1
      Sheets(i).Range("A9").Value = k
    End If
  Next i
End Sub

 

[Jriley_11]

Peter,
This works great! Thank you so much for helping me. I can now move forward with my workbook. I had the right idea but did not use I=1 to sheets.count (just had I=1 and only used 1 variable). I am a beginner with vb code so I have much to learn. Thanks again for your time!

 

[Peter_SSs]

Peter,
This works great! Thank you so much for helping me. I can now move forward with my workbook. I had the right idea but did not use I=1 to sheets.count (just had I=1 and only used 1 variable). I am a beginner with vb code so I have much to learn. Thanks again for your time!

You are very welcome. Just stick at it (& hang around this forum when you can) & you should find progress fairly quick.