Scroll thru multiple spreadsheets
Well I am almost there excep for one little issue, the
final portion of the code which is meant to resize the
columns even though it is within the in statement only
works on the menu screen
Dim value1, value2, value3 As String
Dim LastCol As Range
Dim oCell As Range
value1 = Sheets("Menu").Range("G22")
value2 = Sheets("Menu").Range("G23")
value3 = Sheets("Menu").Range("G24")
'this Code will allow the user to automatically refresh
multiple sheets on a spreadsheet
'without having to go to each one individually
Application.ScreenUpdating = True
Dim objWs As Worksheet, intInc As Integer
For Each objWs In ThisWorkbook.Worksheets
objWs.Activate
With objWs
If Not .Name = "Menu" Then
.Cells(2, 2).End(xlToRight).Offset(0, 1) =
value1
.Cells(3, 2).End(xlToRight).Offset(0, 1) =
value2
.Cells(4, 2).End(xlToRight).Offset(0, 1) =
value3
Application.Run Macro:=("EssMenuRetrieve")
Set LastCol = Cells(2, Columns.Count).End
(xlToLeft)
Set rng = Range(Cells(2, 2), LastCol)
For Each oCell In rng
If oCell = "" Then
oCell.ColumnWidth = 12
Else
oCell.ColumnWidth = 1
End If
Next oCell
End If
End With
Next objWs
|