Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
I want a value to be written to same cell of all the sheets. I have
following macro written. =========== Sub All_Sheets_Loop() Dim i As Integer Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next End Sub =========== Above macro writes only in the active sheet and other sheets are not written by the value. If I add a line activate.sheet it gives error. Any thoughts pl? Shetty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
ActiveSheet.Range("A1").Value = 10
Try sh.Range("A1").Value = 10 "Shetty" wrote in message om... For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next Above macro writes only in the active sheet and other sheets are not written by the value. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
Shetty
Try this Sub All_Sheets_Loop() Dim i As Integer Dim sh i = Worksheets.Count For sh = 1 To i Sheets(sh).Range("A1").Value = 10 Next End Sub Regards Peter -----Original Message----- I want a value to be written to same cell of all the sheets. I have following macro written. =========== Sub All_Sheets_Loop() Dim i As Integer Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next End Sub =========== Above macro writes only in the active sheet and other sheets are not written by the value. If I add a line activate.sheet it gives error. Any thoughts pl? Shetty . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
Explanation
For Each sh in Worksheets does not activate the sheet, that is why you cannot use ActiveSheet. It just sets the sh object at the next sheet in the collection, thus you reference the sheet through this object. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sriram N A" wrote in message ... ActiveSheet.Range("A1").Value = 10 Try sh.Range("A1").Value = 10 "Shetty" wrote in message om... For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next Above macro writes only in the active sheet and other sheets are not written by the value. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
Sub All_Sheets_Loop() Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.Range("A1").Value = 10 Next End Sub 'sh' is the worksheet that you want to work on. No need for it to be the active sheet, and in your code, activesheet refers to the er, active sheet only. Patrick Molloy Microsoft Excel MVP -----Original Message----- I want a value to be written to same cell of all the sheets. I have following macro written. =========== Sub All_Sheets_Loop() Dim i As Integer Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next End Sub =========== Above macro writes only in the active sheet and other sheets are not written by the value. If I add a line activate.sheet it gives error. Any thoughts pl? Shetty . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
I just created this using the macro recorder to select all sheets.
Just change one line as shown by removinging the one with the comment Sub Macro1() ' Macro recorded 1/13/2004 by Don Guillett ' Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select sheets.select ' selects all instead of array above Sheets("Sheet1").Activate Range("A1").Select ActiveCell.FormulaR1C1 = "3" Sheets("Sheet2").Select End Sub -- Don Guillett SalesAid Software "Shetty" wrote in message om... I want a value to be written to same cell of all the sheets. I have following macro written. =========== Sub All_Sheets_Loop() Dim i As Integer Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next End Sub =========== Above macro writes only in the active sheet and other sheets are not written by the value. If I add a line activate.sheet it gives error. Any thoughts pl? Shetty |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop - for each sheet not working?
It worked great. Thanks to all of you for your help.
Regards, "Don Guillett" wrote in message ... I just created this using the macro recorder to select all sheets. Just change one line as shown by removinging the one with the comment Sub Macro1() ' Macro recorded 1/13/2004 by Don Guillett ' Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select sheets.select ' selects all instead of array above Sheets("Sheet1").Activate Range("A1").Select ActiveCell.FormulaR1C1 = "3" Sheets("Sheet2").Select End Sub -- Don Guillett SalesAid Software "Shetty" wrote in message om... I want a value to be written to same cell of all the sheets. I have following macro written. =========== Sub All_Sheets_Loop() Dim i As Integer Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets ActiveSheet.Range("A1").Value = 10 Next End Sub =========== Above macro writes only in the active sheet and other sheets are not written by the value. If I add a line activate.sheet it gives error. Any thoughts pl? Shetty |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop all Sheets not working. | New Users to Excel | |||
On Error GoTo Label in a loop only working once. | Charts and Charting in Excel | |||
Another loop & sheet question | Excel Programming | |||
Using UsedRange as limits in a For Each loop but for cells on another sheet ? | Excel Programming | |||
Excluding sheet in loop | Excel Programming |