Simple For Each Next code
 

"jacqui" wrote in message
...
I'm trying to write a simple For Each statement to loop
through each worksheet in the active workbook. I should
know how to do this but for some reason I'm having a bit
of trouble. Can anyone help? The code I've written so
far is below. I'm getting a VBA error though on the shts
() = Sheets(iCnt) line where Excel says 'Can't assign to
array'.
I'm sure it could also be simpler too, so if there's a
much neater way, I'd be very grateful for any other
suggestions.
Many thanks
Jacqui

Dim iCnt As Integer

Dim sRTitle As String
Dim sht() As Worksheet

ActiveWindow.SelectedSheets.Copy

ActiveWindow.Caption = "ERS-Formula to value"
Application.StatusBar = "Converting formula to
values"

iCnt = Worksheets.Count
sht() = Sheets(iCnt)

For Each sht(iCnt) In Worksheets

EndRow = Range("A65000").End(xlUp).Row + 1
iCCount = Range("IV7").End(xlToLeft).Column

Range("A1", Cells(EndRow, iCCount)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

iCnt = iCnt + 1

Next


The variable sht needs to be an object not an array
and you need to iterate through each one in turn
in the Workbook,something like this

Dim mysht as Worksheet

For Each mysht in ActiveWorkbook.Worksheets

Msgbox mysht.name

Next mysht

Set mysht = Nothing

Keith