Increment value on multiple visible worksheets

[Krle]

I do not have much experience with macros and I am trying to create one to add a increment value to a cell only in visible sheets.
The macro below adds values on all sheets. How to change to add value only to visible sheets?

Sub Test()

Dim wb As Workbook, sh As Worksheet
Dim ws As Long
Dim mynum As Long
mynum = -1

For Each sh In ActiveWorkbook.Sheets
For ws = 1 To Worksheets.count
With Worksheets(ws).Range("Q1")
If sh.Visible Then
.Value = mynum - 1 + ws
End If
End With
Next ws
Next sh
End Sub

 

[mrshl9898]

Try:

Sub Test()

Dim wb As Workbook, sh As Worksheet
Dim ws As Long
Dim mynum As Long
mynum = -1

For Each sh In ActiveWorkbook.Sheets
For ws = 1 To Worksheets.count
If sh.Visible = -1 Then
With Worksheets(ws).Range("Q1")
.Value = mynum - 1 + ws
End With
End If
Next ws
Next sh
End Sub

 

[Krle]

Thanks, but no change, loop does not add value only to the visible sheets!

 

[mrshl9898]

This is working, got rid of sh.

note sheet1 won't change -1-1+1 =-1

Sub Test()

Dim wb As Workbook
Dim ws As Long
Dim mynum As Long
mynum = -1

For ws = 1 To Worksheets.Count
If Worksheets(ws).Visible = -1 Then
With Worksheets(ws).Range("Q1")
.Value = mynum - 1 + ws
End With
End If
Next ws
End Sub

 

[Krle]

This is ok, but there is another problem. It does not add value to hidden sheets, but still counts hidden tabs and increment value is not correct!
Can you exclude hidden sheets and add sequential values only for visible tabs?

 

[mrshl9898]

So first sheet = 1, second = 2 etc?

Sub Test()

Dim wb As Workbook
Dim ws As Long
Dim mynum As Long
mynum = 1

For ws = 1 To Worksheets.Count
If Worksheets(ws).Visible = -1 Then
With Worksheets(ws).Range("Q1")
.Value = mynum
mynum = mynum + 1
End With
End If
Next ws
End Sub

 

[Krle]

So first sheet = 1, second = 2 etc?

Sub Test()

Dim wb As Workbook
Dim ws As Long
Dim mynum As Long
mynum = 1

For ws = 1 To Worksheets.Count
If Worksheets(ws).Visible = -1 Then
With Worksheets(ws).Range("Q1")
.Value = mynum
mynum = mynum + 1
End With
End If
Next ws
End Sub

Yes I need first visible Sheet value 1, second visible 2 etc.
The last code add same number for all visible sheets!

 

[mrshl9898]

Not here... mynum = mynum + 1. Try stepping through the code

 

[Krle]

I try step into code, but can't find what is wrong