Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple code for shifting | Excel Discussion (Misc queries) | |||
Help with some simple code | Excel Discussion (Misc queries) | |||
Simple Code, I think... | Excel Worksheet Functions | |||
Simple Checkbox Message Code | Excel Discussion (Misc queries) | |||
Simple For Each Next code | Excel Programming |