Excel - Changing a formula on multiple sheets 1

[RITec]

Thank you for reading this.

I hope there is an easy way to do this.

I have 12 work books (all in same folder)

Each workbook contains 2-20 sheets.
I have a cell located on each sheet that has the following formula "IF(I107,-1,0)" I107 references a check box (True/False) somewhere else on the page. It is also one of five cells added to a final total in that column. Etc


I need to change the -1 to 1 on all worksheets is there an easy way to do this? or does this need to be done manually?

 

[vbsgtd]

is the formula that needs to be changed located in the same cell on each worksheet? If so, use group mode to change all the sheets within each workbook at one time. are you familiar with group mode?

 

[DaleWatson123321]

Hi RITec,

The following routine should be close to what you want.

It assumes that the formulas in each sheet are located in the SAME positon. You'll need to modify the line:
Range("J1&quot.Formula = "=IF(I107,1,0)"
in the Sheets_ChangeFormua subroutine.

It will:
a) Open a Workbook Open Window, for you to select each file.
b) Close other workbooks that might be in memory, including the PERSONAL.xls file
c) Change the formulas in ALL the sheets of the file you opened
d) Save the file

For activation and re-activation, assign a shortcut key, such as <Control> W

To assign the shortcut, use:
a) Hold down <Alt> and hit <F8>
b) With &quot;Get_Workbook&quot; highlighted, Click &quot;Options&quot;
c) Under &quot;Shortcut key&quot;, enter a (lowercase) &quot;w&quot;.

Here's the routine...

Sub Get_Workbook()
Application.ScreenUpdating = False
wkbk = Workbooks.Application.ActiveWorkbook.Name
OtherWorkbooks_Close
Application.Dialogs(xlDialogOpen).Show
numwk = Workbooks.Count
If numwk < 2 Then Exit Sub
wkbk_change = ActiveWorkbook.Name
Sheets_ChangeFormula
Workbooks(wkbk_change).Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

Sub Sheets_ChangeFormula()
shtCnt = ActiveWorkbook.Sheets.Count
For i = 1 To shtCnt
sht = Worksheets(i).Name
Worksheets(sht).Select
Range(&quot;J1&quot.Formula = &quot;=IF(I107,1,0)&quot;
Next
Worksheets(1).Select
'ActiveWorkbook.Save
End Sub

Sub OtherWorkbooks_Close()
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
If w.Name <> &quot;PERSONAL.xls&quot; Then
End If
w.Close SaveChanges:=True
End If
Next w
End Sub

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[RITec]

Hi VBSGTD
Hi Dale

Just to let everyone know I am new Excel and to vbs.

To answer vbsgtd - question, no I am not familiar with group mode?


Dale - I seem to have a bit more luck with access vbs than I do with Excel.

I know how to open the Visual Basic Editor but not much more beyond that. I don't find the Sheets_ChangeFormula subroutine.

I have clicked on &quot;Thisworkbook under vba project&quot; but I don't think I am in the right area, because I don't see that option under procedure.

I appreciate both your responses but I am afraid that I require little more (maybe a lot more information) help.

Thank you again.

Ritec

 

[DaleWatson123321]

Hi RITec,

Steps to use:

1) Enter Visual Basic Editor. You said you know how, but ...<Alt> <F11> is the keyboard shortcut.

2) Once in VB Editor, from the menu, choose: Insert - Module.

3) Go to the Tek-Tips posting by using <Alt> <Tab> (Hold down <Alt> and hit <Tab>

4) Highlight and Copy the routine I posted previously. This starts below the line &quot;Here's the routine...&quot; and ends at the line above &quot;I hope this helps&quot;. (The routine consists of the opening routine (Get_Workbook) and two subroutines (Sheets_ChangeFormula and OtherWorkbooks_Close)

5) Go back to your Excel VB Editor screen - using <Alt> <Tab>.

6) On the right-side of the screen, Paste the VBA routines you just copied.

7) Make the necessary modification to the line in the &quot;Sheets_ChangeFormula&quot; subroutine that refers to the location of the formula you need to change.
i.e. Range(&quot;J1&quot.Formula = &quot;=IF(I107,1,0)&quot;

Note: I had commented-out the last line of this routine... 'ActiveWorkbook.Save
I had intended to delete this, as the file is saved in the opening routine with the line:
Workbooks(wkbk_change).Close SaveChanges:=True

8) Exit the VB Editor - by using <Alt> Q

In my previous posting, I had suggested you use a &quot;keyboard shortcut&quot; to activate the routine - for each time you want to choose a file.

To assign the shortcut, use:
a) Hold down <Alt> and hit <F8>
b) With &quot;Get_Workbook&quot; highlighted, Click &quot;Options&quot;
c) Under &quot;Shortcut key&quot;, enter a (lowercase) &quot;w&quot;.

Note: If you happen to have your &quot;PERSONAL.xls&quot; workbook open (even if you have it hidden)... After step &quot;a)&quot; above, you will need to change &quot;Macros in: All Open Workbooks&quot; to &quot;Macros in: This Workbook&quot;. (see center-bottom of the Macro window).

Then click on the &quot;Get_Workbook&quot; routine.
Then click on &quot;Options&quot;.
Then under &quot;Shortcut key&quot;, enter a lowercase &quot;w&quot;.

Click OK, and close the Macro window.
Save your modified file.

You can now begin to modify each of the 12 files, by holding down <Ctrl> and hitting &quot;w&quot;. You just need to choose the file, and the macro will do the rest.

You only need to re-activate the macro each time you want to choose another file.

I hope this further explanation helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

P.S. I'll be out-of-town for the next week, so if you require further help before I return, perhaps someone else can assist.

 

[RITec]

Hi Dale

worked great !!!


Is there a way to unprotect all the sheets at once


Thank You Again

 

[RITec]

I should mention that every sheet in every workbook has a different name.

 

[JR2913]

Try this subroutine:

Sub Unprotectsheets()

Dim mysheet As Worksheet

For Each mysheet In Worksheets
mysheet.Unprotect &quot;password&quot;
Next mysheet

End Sub

 

[RITec]

I will give it a try and let you know

Thank you for responding

Ritec

 

[RITec]

JR2913

Worked great Thank you.

Ritec