Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Code Not Working
I've had a couple of replies to my earlier question but unfortunately the solutions do not work. The code runs through the loop and converts my formula to values for the first worksheet but when it gets to next sht, the code is not selecting the next sheet in the workbook. In stepping through I've noted the value of my variable sht = nothing. Surely I must need to set this to something. I'm confused. Can anyone help? My code is below. 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 For Each sht In ActiveWorkbook.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 Next sht |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Code Not Working
"jacqui" wrote in message ... I've had a couple of replies to my earlier question but unfortunately the solutions do not work. The code runs through the loop and converts my formula to values for the first worksheet but when it gets to next sht, the code is not selecting the next sheet in the workbook. In stepping through I've noted the value of my variable sht = nothing. Surely I must need to set this to something. I'm confused. Can anyone help? My code is below. Many thanks Jacqui You arent explicitly telling the application which sheet to select the ranges on so it assumes you mean the active sheet (the one you ran the macro from) If you prefix the range with the Sheet object all will be well i.e. Dim sht As Worksheet For Each sht In ActiveWorkbook.Worksheets with sht EndRow = .Range("A65000").End(xlUp).Row + 1 iCCount = .Range("IV7").End(xlToLeft).Column .Range("A1", Cells(EndRow, iCCount)).Select etc End With Next sht Keith 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 For Each sht In ActiveWorkbook.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 Next sht |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Code Not Working
Hi,
I think this may be a case where you need to to activate each sheet. Regards, Don Lloyd. -- "jacqui" wrote in message ... I've had a couple of replies to my earlier question but unfortunately the solutions do not work. The code runs through the loop and converts my formula to values for the first worksheet but when it gets to next sht, the code is not selecting the next sheet in the workbook. In stepping through I've noted the value of my variable sht = nothing. Surely I must need to set this to something. I'm confused. Can anyone help? My code is below. 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 For Each sht In ActiveWorkbook.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 Next sht |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Code Not Working
jacqui,
add this line For Each sht In ActiveWorkbook.Worksheets sht.Activate EndRow = Range("A65000").End(xlUp).Row + 1 HTH Cecil "jacqui" wrote in message ... I've had a couple of replies to my earlier question but unfortunately the solutions do not work. The code runs through the loop and converts my formula to values for the first worksheet but when it gets to next sht, the code is not selecting the next sheet in the workbook. In stepping through I've noted the value of my variable sht = nothing. Surely I must need to set this to something. I'm confused. Can anyone help? My code is below. 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 For Each sht In ActiveWorkbook.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 Next sht |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Code Not Working
I like Keith's suggestion, but I think he missed a dot and you can't select a
range on a sheet that isn't active. But this seemed to work ok for me: Option Explicit Sub testme00() Dim iCnt As Long Dim EndRow As Long Dim iCCount As Long 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 'delete this? For Each sht In ActiveWorkbook.Worksheets With sht EndRow = .Range("A65000").End(xlUp).Row + 1 iCCount = .Range("IV7").End(xlToLeft).Column With .Range("A1", .Cells(EndRow, iCCount)) .Copy .PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End With End With Next sht End Sub jacqui wrote: I've had a couple of replies to my earlier question but unfortunately the solutions do not work. The code runs through the loop and converts my formula to values for the first worksheet but when it gets to next sht, the code is not selecting the next sheet in the workbook. In stepping through I've noted the value of my variable sht = nothing. Surely I must need to set this to something. I'm confused. Can anyone help? My code is below. 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 For Each sht In ActiveWorkbook.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 Next sht -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code Not Working | Excel Discussion (Misc queries) | |||
VBA Code Not Working | Excel Discussion (Misc queries) | |||
Code not working as expected. | Excel Discussion (Misc queries) | |||
VB Code Is Not Working | Excel Discussion (Misc queries) | |||
Code not working and can't see why | Excel Discussion (Misc queries) |