| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing problems with hidden rows | Excel Worksheet Functions | |||
| hidden sheets | Excel Discussion (Misc queries) | |||
| Strange problems with setting hidden property of a range when sheet protected with UserInterfaceOnly | Excel Programming | |||
| "With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets | Excel Programming | |||
| Hidden sheets | Excel Programming | |||
Linear Mode
