|
Automatically update all worksheets
Hi AliH
You can right click on a sheet tab and select all sheets Change the cell and right click on a sheet tab and ungroup the sheets With code Sub test() Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.Range("G14").Value = "Hello" Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Hi Ali,
Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
I have a workbook with lots of worksheets that all have the same layout and
are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Hi Ali,
Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Thanks for the help but it wont accept my formula string.
The message I get is RunTIme error 1004 Application-defined or object defined error Any ideas what is wrong with this? It works ok if I just type it into the cell wks.Range("G15").Formula = "=IF(C16='Enter Manual %age Below',C17,IF(B17='Y',C17,C16))" Thanks again "Norman Jones" wrote in message ... Hi Ali, Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Thanks for the help but it wont accept my formula string. That is is because there are problems with your formula string. Firstly, no qiuotes are needed in the substring: IF(B17='Y',C17,C16) Secondly, where quotes are needed, it is necessary, in VBA, to double them. Adjusting for these points, the following worked for me: ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below "",C17,IF(B17=Y,C17,C16))" --- Regards, Norman "AliH" wrote in message ... Thanks for the help but it wont accept my formula string. The message I get is RunTIme error 1004 Application-defined or object defined error Any ideas what is wrong with this? It works ok if I just type it into the cell wks.Range("G15").Formula = "=IF(C16='Enter Manual %age Below',C17,IF(B17='Y',C17,C16))" Thanks again "Norman Jones" wrote in message ... Hi Ali, Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Thanks that works although I did need to have "" around the Y in the second
IF statement. When the sheet was protected I had not allowed for the Locked cells to be selected. Now that the sheet is protected using the code it has defaulted back to allowing locked cells to be selected. How can I turn this off without going into each sheet? "Norman Jones" wrote in message ... Thanks for the help but it wont accept my formula string. That is is because there are problems with your formula string. Firstly, no qiuotes are needed in the substring: IF(B17='Y',C17,C16) Secondly, where quotes are needed, it is necessary, in VBA, to double them. Adjusting for these points, the following worked for me: ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below "",C17,IF(B17=Y,C17,C16))" --- Regards, Norman "AliH" wrote in message ... Thanks for the help but it wont accept my formula string. The message I get is RunTIme error 1004 Application-defined or object defined error Any ideas what is wrong with this? It works ok if I just type it into the cell wks.Range("G15").Formula = "=IF(C16='Enter Manual %age Below',C17,IF(B17='Y',C17,C16))" Thanks again "Norman Jones" wrote in message ... Hi Ali, Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Hi Ali,
Thanks that works although I did need to have "" around the Y in the second IF statement. Indeed you did, all quotes need to be doubled - my aberration. When the sheet was protected I had not allowed for the Locked cells to be selected. Now that the sheet is protected using the code it has defaulted back to allowing locked cells to be selected. How can I turn this off without going into each sheet? Try something like: '====================== Sub Tester05A() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.EnableSelection = xlUnlockedCells ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below ""," _ & "C17,IF(B17=""Y"",C17,C16))" wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... Thanks that works although I did need to have "" around the Y in the second IF statement. When the sheet was protected I had not allowed for the Locked cells to be selected. Now that the sheet is protected using the code it has defaulted back to allowing locked cells to be selected. How can I turn this off without going into each sheet? "Norman Jones" wrote in message ... Thanks for the help but it wont accept my formula string. That is is because there are problems with your formula string. Firstly, no qiuotes are needed in the substring: IF(B17='Y',C17,C16) Secondly, where quotes are needed, it is necessary, in VBA, to double them. Adjusting for these points, the following worked for me: ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below "",C17,IF(B17=Y,C17,C16))" --- Regards, Norman "AliH" wrote in message ... Thanks for the help but it wont accept my formula string. The message I get is RunTIme error 1004 Application-defined or object defined error Any ideas what is wrong with this? It works ok if I just type it into the cell wks.Range("G15").Formula = "=IF(C16='Enter Manual %age Below',C17,IF(B17='Y',C17,C16))" Thanks again "Norman Jones" wrote in message ... Hi Ali, Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
Automatically update all worksheets
Thanks -works a treat!
"Norman Jones" wrote in message ... Hi Ali, Thanks that works although I did need to have "" around the Y in the second IF statement. Indeed you did, all quotes need to be doubled - my aberration. When the sheet was protected I had not allowed for the Locked cells to be selected. Now that the sheet is protected using the code it has defaulted back to allowing locked cells to be selected. How can I turn this off without going into each sheet? Try something like: '====================== Sub Tester05A() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.EnableSelection = xlUnlockedCells ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below ""," _ & "C17,IF(B17=""Y"",C17,C16))" wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... Thanks that works although I did need to have "" around the Y in the second IF statement. When the sheet was protected I had not allowed for the Locked cells to be selected. Now that the sheet is protected using the code it has defaulted back to allowing locked cells to be selected. How can I turn this off without going into each sheet? "Norman Jones" wrote in message ... Thanks for the help but it wont accept my formula string. That is is because there are problems with your formula string. Firstly, no qiuotes are needed in the substring: IF(B17='Y',C17,C16) Secondly, where quotes are needed, it is necessary, in VBA, to double them. Adjusting for these points, the following worked for me: ActiveSheet.Range("G15").Value = _ "=IF(C16=""Enter Manual %age Below "",C17,IF(B17=Y,C17,C16))" --- Regards, Norman "AliH" wrote in message ... Thanks for the help but it wont accept my formula string. The message I get is RunTIme error 1004 Application-defined or object defined error Any ideas what is wrong with this? It works ok if I just type it into the cell wks.Range("G15").Formula = "=IF(C16='Enter Manual %age Below',C17,IF(B17='Y',C17,C16))" Thanks again "Norman Jones" wrote in message ... Hi Ali, Change: wks.Protect Password = PWORD to wks.Protect Password:=PWORD I missed the required colon! --- Regards, Norman "Norman Jones" wrote in message ... Hi Ali, Try something like: '====================== Sub Tester05() Dim wks As Worksheet Static PWORD As String PWORD = InputBox("Please Enter Password") On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=PWORD wks.Range("G14").Formula = "???" '<<==== Your Formula wks.Protect Password = PWORD Next End Sub '<<====================== --- Regards, Norman "AliH" wrote in message ... I have a workbook with lots of worksheets that all have the same layout and are protected. I want to make a change to a fomula in cell g14 on all the spreadsheets How can I do this? |
| All times are GMT +1. The time now is 03:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com