Simple For Each Next code
 

Uzytkownik "jacqui" napisal w wiadomosci
...
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

It isn't an array

Dim sht As Worksheet


ActiveWindow.SelectedSheets.Copy

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

iCnt = Worksheets.Count
delete this line sht() = Sheets(iCnt)



For Each sht 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

delete this line iCnt = iCnt + 1

Next sht




MS