Code - Possible to turn into a LOOP ?

[Hodgy001]

Hello, as I am a beginner I was hoping someone may be able to help with the following:

I have the following code which runs and hides specific columns relating to whether they are required or not on each sheet, based on an overall template sheet.

As there are many sheets and columns, the code will be very long and I was wondering if it could be incorporated into a loop to shorten and simplify the code??? I have tried but have had no luck with this!

Current Code:

Private Sub OpenSheets()

Sheets("Ward 1").Select

If Cells(7, 7) = "n/a" Then
Columns("G:G").Select
Selection.EntireColumn.Hidden = True

ElseIf Cells(7, 7) = "" Then
Columns("G:G").Select
Selection.EntireColumn.Hidden = False

End If

If Cells(7, 8) = "n/a" Then
Columns("H:H").Select
Selection.EntireColumn.Hidden = True

ElseIf Cells(7, 8) = "1" Then
Columns("h:h").Select
Selection.EntireColumn.Hidden = False
End If

AND SO ON...FOR EACH COLUMN AND SHEET !!

End Sub

Any help would be greatly appreciated,

 

[PHV]

A starting point:
For i = 7 To 50
Columns(i).EntireColumn.Hidden = (Cells(7, i) = "n/a")
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hodgy001]

Thanks PHV,

That is just what I was looking for, I will now look at applying it to each sheet I have.

Much appreciated,

 

[xlbo]

Have a look at putting it in a For Each...Next loop through the sheets collection

eg

For each sht in thisworkbook.worksheets
'perform action with 'sht' object
Next