WHy does it only work on Menu SHeet
 

The following Code is designed to scroll thru all the
sheets in a workbook and update them, the last part of the
code only works on the menu sheet it has no effect on the
other worksheets.

I have 2 questions

1. Rather than looking thru a range of cells on the menu
sheet for the sheet name is their a way I can just go thru
each worksheet in a book and process it AND

2. Why does the Code only work on the menu sheet when in
theory it is not the active sheet

Any questions please feel free to email me

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate
If Not Sheets("Menu").Range("G22") Is Nothing Then
Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
value1
Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
value2
Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
value3
End If

Application.Run Macro:=("EssMenuRetrieve")


Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
Set rng = Range("b2", LastCol)

For Each oCell In rng

If oCell < "" Then
oCell.ColumnWidth = 13
Else
oCell.ColumnWidth = 1

End If
Next oCell

Next

WHy does it only work on Menu SHeet
 

qualify your range references with the sheets you want to refer to.

--
Regards,
Tom Ogilvy

"Nigel Bennett" wrote in message
...
The following Code is designed to scroll thru all the
sheets in a workbook and update them, the last part of the
code only works on the menu sheet it has no effect on the
other worksheets.

I have 2 questions

1. Rather than looking thru a range of cells on the menu
sheet for the sheet name is their a way I can just go thru
each worksheet in a book and process it AND

2. Why does the Code only work on the menu sheet when in
theory it is not the active sheet

Any questions please feel free to email me

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate
If Not Sheets("Menu").Range("G22") Is Nothing Then
Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
value1
Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
value2
Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
value3
End If

Application.Run Macro:=("EssMenuRetrieve")


Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
Set rng = Range("b2", LastCol)

For Each oCell In rng

If oCell < "" Then
oCell.ColumnWidth = 13
Else
oCell.ColumnWidth = 1

End If
Next oCell

Next

WHy does it only work on Menu SHeet
 

Hi,
I'm not entirely sure what you are trying to achieve here so perhaps a
better explanation will result in a better response but this should get
you started...

This code will loop thru all worksheets not called Menu and perform the
same operation on each sheet. In your example you don't tell us
what/where value1, value2 etc come from...

Dim objWs As Worksheet, intInc As Integer

For Each objWs In ThisWorkbook.Worksheets
With objWs
If Not .Name = "Menu" Then
.Cells(2, 2).End(xlToRight)(1, 2).Value = Value1
.Cells(3, 2).End(xlToRight)(1, 2).Value = Value2
.Cells(4, 2).End(xlToRight)(1, 2).Value = Value3
Call EssMenuRetrieve
End If
End With
Next objWs


hth,
OJ