problems with hidden sheets in for each
 

Hey People!

I have a for inside another for. It searches factories and then production
lines. As the number of production lines is different in each factory's file,
I have to use the for each command.

I wrote a code as follows:

For Each obj In Sheets
Sheets(i).Select
feuil = ActiveSheet.Name
'ActiveWindow.SelectedSheets.PrintOut
MsgBox ("Le " & feuil & " du " & "0703_S03_" & usi & " a été
imprimé")
i = i + 1
Next obj

The problem is that there are hidden sheets and the For command consider
them too. So it bugs in the shhet(i).select command. I tryed puting "For Each
obj.visible = true In Sheets", but VBA did not accept either.

Do you have any ideas??? Thanks in advance.

Daniel (Brazil)

problems with hidden sheets in for each
 

The easiest solution is not .select objects before working them

Private Sub CommandButton2_Click()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
With WS
If .Visible = xlSheetVisible Then
.PrintOut
MsgBox "Le " & .Name & " du " & "0703_S03_" & usi & " a été
imprimé"
i = i + 1
End If
End With
Next 'WS

Not sure what i and usi do here though...

NickHK

"dspilberg" wrote in message
...
Hey People!

I have a for inside another for. It searches factories and then production
lines. As the number of production lines is different in each factory's
file,
I have to use the for each command.

I wrote a code as follows:

For Each obj In Sheets
Sheets(i).Select
feuil = ActiveSheet.Name
'ActiveWindow.SelectedSheets.PrintOut
MsgBox ("Le " & feuil & " du " & "0703_S03_" & usi & " a
été
imprimé")
i = i + 1
Next obj

The problem is that there are hidden sheets and the For command consider
them too. So it bugs in the shhet(i).select command. I tryed puting "For
Each
obj.visible = true In Sheets", but VBA did not accept either.

Do you have any ideas??? Thanks in advance.

Daniel (Brazil)

problems with hidden sheets in for each
 

Nick, thanks.

I used your idea to arrive to the final solution. Here it goes!

i = 1
For Each WS In Worksheets
With WS
If .Visible = xlSheetVisible Then
'.PrintOut
Sheets(i).Select
MsgBox "Le " & .Name & " du " & "0703_S03_" &
usi & " a été imprimé "
End If
End With
i = i + 1
Next WS


"NickHK" wrote:

The easiest solution is not .select objects before working them

Private Sub CommandButton2_Click()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
With WS
If .Visible = xlSheetVisible Then
.PrintOut
MsgBox "Le " & .Name & " du " & "0703_S03_" & usi & " a été
imprimé"
i = i + 1
End If
End With
Next 'WS

Not sure what i and usi do here though...

NickHK

"dspilberg" wrote in message
...
Hey People!

I have a for inside another for. It searches factories and then production
lines. As the number of production lines is different in each factory's
file,
I have to use the for each command.

I wrote a code as follows:

For Each obj In Sheets
Sheets(i).Select
feuil = ActiveSheet.Name
'ActiveWindow.SelectedSheets.PrintOut
MsgBox ("Le " & feuil & " du " & "0703_S03_" & usi & " a
été
imprimé")
i = i + 1
Next obj

The problem is that there are hidden sheets and the For command consider
them too. So it bugs in the shhet(i).select command. I tryed puting "For
Each
obj.visible = true In Sheets", but VBA did not accept either.

Do you have any ideas??? Thanks in advance.

Daniel (Brazil)

problems with hidden sheets in for each
 

As I said, you do NOT need to .Select the sheet.
You in effect have 2 loop; the "For Each" and the "i=i+1". This i counter
does nothing useful. That's why I questioned its use in your original code.
You can delete it from my code as it is not used.
Indeed the Sheets collections may not be the same as the Worksheets
collection as the former includes Charts sheets and Macro sheets also.

NickHK

"dspilberg" wrote in message
...
Nick, thanks.

I used your idea to arrive to the final solution. Here it goes!

i = 1
For Each WS In Worksheets
With WS
If .Visible = xlSheetVisible Then
'.PrintOut
Sheets(i).Select
MsgBox "Le " & .Name & " du " & "0703_S03_" &
usi & " a été imprimé "
End If
End With
i = i + 1
Next WS


"NickHK" wrote:

The easiest solution is not .select objects before working them

Private Sub CommandButton2_Click()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
With WS
If .Visible = xlSheetVisible Then
.PrintOut
MsgBox "Le " & .Name & " du " & "0703_S03_" & usi & " a été
imprimé"
i = i + 1
End If
End With
Next 'WS

Not sure what i and usi do here though...

NickHK

"dspilberg" wrote in message
...
Hey People!

I have a for inside another for. It searches factories and then
production
lines. As the number of production lines is different in each
factory's
file,
I have to use the for each command.

I wrote a code as follows:

For Each obj In Sheets
Sheets(i).Select
feuil = ActiveSheet.Name
'ActiveWindow.SelectedSheets.PrintOut
MsgBox ("Le " & feuil & " du " & "0703_S03_" & usi & "
a
été
imprimé")
i = i + 1
Next obj

The problem is that there are hidden sheets and the For command
consider
them too. So it bugs in the shhet(i).select command. I tryed puting
"For
Each
obj.visible = true In Sheets", but VBA did not accept either.

Do you have any ideas??? Thanks in advance.

Daniel (Brazil)

problems with hidden sheets in for each
 

Nick,

I see what you say. I made the changes you suggested and used Sheets instead
of Worksheets. It is perfectly working. Thanks once again.

Daniel


"NickHK" wrote:

As I said, you do NOT need to .Select the sheet.
You in effect have 2 loop; the "For Each" and the "i=i+1". This i counter
does nothing useful. That's why I questioned its use in your original code.
You can delete it from my code as it is not used.
Indeed the Sheets collections may not be the same as the Worksheets
collection as the former includes Charts sheets and Macro sheets also.

NickHK

"dspilberg" wrote in message
...
Nick, thanks.

I used your idea to arrive to the final solution. Here it goes!

i = 1
For Each WS In Worksheets
With WS
If .Visible = xlSheetVisible Then
'.PrintOut
Sheets(i).Select
MsgBox "Le " & .Name & " du " & "0703_S03_" &
usi & " a été imprimé "
End If
End With
i = i + 1
Next WS


"NickHK" wrote:

The easiest solution is not .select objects before working them

Private Sub CommandButton2_Click()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
With WS
If .Visible = xlSheetVisible Then
.PrintOut
MsgBox "Le " & .Name & " du " & "0703_S03_" & usi & " a été
imprimé"
i = i + 1
End If
End With
Next 'WS

Not sure what i and usi do here though...

NickHK

"dspilberg" wrote in message
...
Hey People!

I have a for inside another for. It searches factories and then
production
lines. As the number of production lines is different in each
factory's
file,
I have to use the for each command.

I wrote a code as follows:

For Each obj In Sheets
Sheets(i).Select
feuil = ActiveSheet.Name
'ActiveWindow.SelectedSheets.PrintOut
MsgBox ("Le " & feuil & " du " & "0703_S03_" & usi & "
a
été
imprimé")
i = i + 1
Next obj

The problem is that there are hidden sheets and the For command
consider
them too. So it bugs in the shhet(i).select command. I tryed puting
"For
Each
obj.visible = true In Sheets", but VBA did not accept either.

Do you have any ideas??? Thanks in advance.

Daniel (Brazil)