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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 03:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com