Scroll thru multiple spreadsheets
Nigel,
a couple of points..
1. Dim value1, value2, value3 As String ''This line will only dim
value3 as a string, the others will be variant. Should read:
Dim Value1 as String, Value2 as String, Value3 as String
2. You need to qualify these statements with a sheet....since you are
already referencing a sheet with the With Statement then try this
For Each objWs In ThisWorkbook.Worksheets
With objWs
If Not .Name = "Menu" Then
.Activate
.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
With oCell
If .Value = "" Then
.ColumnWidth = 12
Else
.ColumnWidth = 1
End If
End With
Next oCell
End If
End With
Next objWs
Hth
OJ
|