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?

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" <nospamthanks@nospam.com> wrote in message news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
>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?
>
>

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" <nospamthanks@nospam.com> wrote in message
news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
>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?
>
>

Hi Ali,

Change:

> wks.Protect Password = PWORD

to

wks.Protect Password:=PWORD

I missed the required colon!

---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
> 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" <nospamthanks@nospam.com> wrote in message
> news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
>>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?
>>
>>
>
>

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" <normanjones@whereforartthou.com> wrote in message
news:#cj9JxQmFHA.3336@tk2msftngp13.phx.gbl...
> Hi Ali,
>
> Change:
>
> > wks.Protect Password = PWORD
>
> to
>
> wks.Protect Password:=PWORD
>
> I missed the required colon!
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
> > 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" <nospamthanks@nospam.com> wrote in message
> > news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
> >>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?
> >>
> >>
> >
> >
>
>

> 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" <nospamthanks@nospam.com> wrote in message
news:OzpN2GZmFHA.3448@TK2MSFTNGP12.phx.gbl...
> 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" <normanjones@whereforartthou.com> wrote in message
> news:#cj9JxQmFHA.3336@tk2msftngp13.phx.gbl...
>> Hi Ali,
>>
>> Change:
>>
>> > wks.Protect Password = PWORD
>>
>> to
>>
>> wks.Protect Password:=PWORD
>>
>> I missed the required colon!
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
>> > 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" <nospamthanks@nospam.com> wrote in message
>> > news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
>> >>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?
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <normanjones@whereforartthou.com> wrote in message
news:#NxCgdZmFHA.1044@tk2msftngp13.phx.gbl...
>
> > 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" <nospamthanks@nospam.com> wrote in message
> news:OzpN2GZmFHA.3448@TK2MSFTNGP12.phx.gbl...
> > 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" <normanjones@whereforartthou.com> wrote in message
> > news:#cj9JxQmFHA.3336@tk2msftngp13.phx.gbl...
> >> Hi Ali,
> >>
> >> Change:
> >>
> >> > wks.Protect Password = PWORD
> >>
> >> to
> >>
> >> wks.Protect Password:=PWORD
> >>
> >> I missed the required colon!
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> >> news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
> >> > 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" <nospamthanks@nospam.com> wrote in message
> >> > news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
> >> >>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?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <nospamthanks@nospam.com> wrote in message
news:eGeDhgbmFHA.3960@TK2MSFTNGP12.phx.gbl...
> 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" <normanjones@whereforartthou.com> wrote in message
> news:#NxCgdZmFHA.1044@tk2msftngp13.phx.gbl...
>>
>> > 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" <nospamthanks@nospam.com> wrote in message
>> news:OzpN2GZmFHA.3448@TK2MSFTNGP12.phx.gbl...
>> > 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" <normanjones@whereforartthou.com> wrote in message
>> > news:#cj9JxQmFHA.3336@tk2msftngp13.phx.gbl...
>> >> Hi Ali,
>> >>
>> >> Change:
>> >>
>> >> > wks.Protect Password = PWORD
>> >>
>> >> to
>> >>
>> >> wks.Protect Password:=PWORD
>> >>
>> >> I missed the required colon!
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> >> news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
>> >> > 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" <nospamthanks@nospam.com> wrote in message
>> >> > news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
>> >> >>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?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Thanks -works a treat!
"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:elXWqxbmFHA.2484@TK2MSFTNGP15.phx.gbl...
> 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" <nospamthanks@nospam.com> wrote in message
> news:eGeDhgbmFHA.3960@TK2MSFTNGP12.phx.gbl...
> > 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" <normanjones@whereforartthou.com> wrote in message
> > news:#NxCgdZmFHA.1044@tk2msftngp13.phx.gbl...
> >>
> >> > 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" <nospamthanks@nospam.com> wrote in message
> >> news:OzpN2GZmFHA.3448@TK2MSFTNGP12.phx.gbl...
> >> > 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" <normanjones@whereforartthou.com> wrote in message
> >> > news:#cj9JxQmFHA.3336@tk2msftngp13.phx.gbl...
> >> >> Hi Ali,
> >> >>
> >> >> Change:
> >> >>
> >> >> > wks.Protect Password = PWORD
> >> >>
> >> >> to
> >> >>
> >> >> wks.Protect Password:=PWORD
> >> >>
> >> >> I missed the required colon!
> >> >>
> >> >> ---
> >> >> Regards,
> >> >> Norman
> >> >>
> >> >>
> >> >>
> >> >> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> >> >> news:Ohs%238tQmFHA.3900@TK2MSFTNGP09.phx.gbl...
> >> >> > 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" <nospamthanks@nospam.com> wrote in message
> >> >> > news:u1NmOnQmFHA.3256@tk2msftngp13.phx.gbl...
> >> >> >>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?
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>