Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF TO BUILD SIMPLE CODE | Excel Worksheet Functions | |||
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) |