PDA

View Full Version : Solved: Loop that works on all sheets in workbook



MrNorway
04-10-2011, 08:15 AM
Hello.



I have a problem with my macro. The code is supposed to solve this problem. I manage the budget for 20 departments, each represented by a sheet. Each month I need to unlock each sheet, show hidden colums/rows, and show displayheadings, outlines ect., and then update the worksheet with the latest financial data. After this is done I need a code to reverse the first one.

This code manages to unlock / lock all sheets, and do what I wanted with the activesheet, BUT not the rest. How can I make a “loop” that performs all the tasks on every sheet?

Sub Mastersnitt()
' Mastersnitt Makro
' Makro registrert 24.03.2011 av Roger
Dim Budsjett As Worksheet
For Each Budsjett In Worksheets
With ActiveWindow
.DisplayHeadings = True
.DisplayOutline = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
Next Budsjett
Dim objSheet As Worksheet
For Each objSheet In Worksheets
If objSheet.ProtectContents = True Then objSheet _
.Unprotect "e"
Next objSheet
End Sub

shrivallabha
04-10-2011, 08:32 AM
Welcolme to VBAX!

You can use For each loop which I see that you have already put into use:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'Put your code to be repeated here
Next ws

MrNorway
04-10-2011, 12:22 PM
thank you, i finally found out what was missing, it was the budsjett.activate line. The code that works now:

Sub Brukersnitt()
' Brukersnitt Makro
' Makro registrert 24.03.2011 av Roger
Dim budsjett As Worksheet
For Each budsjett In Sheets
budsjett.Activate
With ActiveWindow
.DisplayHeadings = False
.DisplayOutline = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
Next budsjett
Dim objSheet As Worksheet
For Each objSheet In Worksheets
If objSheet.ProtectContents = False Then objSheet _
.Protect "e"
Next objSheet
End Sub