N
Nigel Bennett
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
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