Excel Macro to change contents of a cell for various workbooks.

I have many worksheets that are structured the same. On each worksheet there is a tab called "Tax". On this tab,  I want to change the contents in cell E91 from 0.00% to 17.77%. Is there code that can 1) allow me to select various worksheets 2) open the first worksheet make the change save and close the worksheet 3) Open the next worksheet and do the change and save until all the worksheet selected have been completed?

Thank you.

Conernesto
ConernestoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Are these worksheets all in the same workbook?
0
ConernestoAuthor Commented:
No. There many workbooks.
0
NorieVBA ExpertCommented:
So you have multiple workbooks which each have a worksheet named 'Tax'?

Are all the workbooks located in the same folder?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ConernestoAuthor Commented:
Yes. and Yes.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
Sub Conernesto()
Dim wb As Workbook
Dim ws As Worksheet
Dim file
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
   .Title = "Select The Files!"
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xls*"
   .AllowMultiSelect = True
   .ButtonName = "Confirm"
   If .Show = -1 Then
        For Each file In .SelectedItems
            If file <> ThisWorkbook.FullName Or InStr(file, ThisWorkbook.FullName) = 0 Then
                Set wb = Workbooks.Open(file)
                On Error Resume Next
                Set ws = wb.Sheets("Tax")
                On Error GoTo 0
                If Not ws Is Nothing Then
                    ws.Range("E91").Value = 0.1777
                    ws.Range("E91").NumberFormat = "0.00%"
                End If
                wb.Close True
            End If
            Set wb = Nothing
            Set ws = Nothing
        Next file
   Else
      MsgBox "You didn't select any file.", vbExclamation, "File Not Selected!"
      Exit Sub
   End If
End With
Application.ScreenUpdating = True
MsgBox "Task completed successfully.", vbInformation, "Done!"
End Sub

Open in new window

0
ConernestoAuthor Commented:
Hi, how do I add this code to a module or Excel object so I can run the code?
0
ConernestoAuthor Commented:
I think I am setting this wrong. Attached is a copy of the compile error I get when I run the code.
CompileError.xlsx
0
ConernestoAuthor Commented:
Can you please try running the code on my workbook?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the workbook with code placed on Module1 and a button called "Click Here" on Sheet1. You may click this button to run the code.
FileWithMacro.xlsm
0
ConernestoAuthor Commented:
Hi, I clicked the button and selected one workbook. I got the message that the task was completed but the cell on E91 did not updated to 17.77%. Attached is a sample of one of my workbooks. Please run your code and see if the cell is updated and saved.
ChangeCell-E91-to-17.77--on-tab-Tax.xlsx
0
ConernestoAuthor Commented:
It appears that the action to save, close and go to the next workbook is missing.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's because the file you attached doesn't contain a sheet called "Tax". It contains a sheet called 'Tax Provision" not "Tax".
If the target sheet's name is Tax Provision instead of just Tax, change it's name in the following line of code (Line#17)...

Set ws = wb.Sheets("Tax Provision")

Open in new window

Then it will change the cell content as desired.

The tweaked code should look like....
Sub Conernesto()
Dim wb As Workbook
Dim ws As Worksheet
Dim file
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
   .Title = "Select The Files!"
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xls*"
   .AllowMultiSelect = True
   .ButtonName = "Confirm"
   If .Show = -1 Then
        For Each file In .SelectedItems
            If file <> ThisWorkbook.FullName Or InStr(file, ThisWorkbook.FullName) = 0 Then
                Set wb = Workbooks.Open(file, False)
                On Error Resume Next
                Set ws = wb.Sheets("Tax Provision")
                On Error GoTo 0
                If Not ws Is Nothing Then
                    ws.Range("E91").Value = 0.1777
                    ws.Range("E91").NumberFormat = "0.00%"
                End If
                wb.Close True
            End If
            Set wb = Nothing
            Set ws = Nothing
        Next file
   Else
      MsgBox "You didn't select any file.", vbExclamation, "File Not Selected!"
      Exit Sub
   End If
End With
Application.ScreenUpdating = True
MsgBox "Task completed successfully.", vbInformation, "Done!"
End Sub

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It appears that the action to save, close and go to the next workbook is missing.
That's not true.
Look at the line#23 where the file is saved and closed and line#27 looks for another file selected.
0
ConernestoAuthor Commented:
Hi, I changed Line #17 per above. When I run the code, It stops at line 21 (see line of code below). This appears to be cousing a bug.

 ws.Range("E91").NumberFormat = "0.00%"
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Are you saying that it didn't work with the sample file you attached? I tested it and it worked without an issue.
Also, there is nothing in that line which would cause an issue.

I am attaching the file after implementing the changes I suggested. Test it and let me know if that works.
Remember that the code assumes that the sheet name in the opened file is "Tax Provision" not "Tax".
FileWithMacro.xlsm
0
ConernestoAuthor Commented:
I am getting a run time error. Attached is the lates file you sent me. On the second tab, I entered a copy of the error that I get when I run the code.
FileWithMacro.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Is the file being opened protected? If so, the code will need to unprotect it before changing the numberformat of the cell.
0
ConernestoAuthor Commented:
The file does have some protections but that particular cell is an input cell that is not protected. Do you want me to send you two more samples you you can test selecting three workbooks?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, attach the files you had problem with. I will test them.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It is 1.37 AM here so I will log off in next 15 minutes.
0
ConernestoAuthor Commented:
We can take a look at this tomorrow. Thank you. Conernesto.
0
ConernestoAuthor Commented:
It's 209 pm my local time.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No problem. Please attach your files and I will look at them tomorrow.
Thanks for your understanding.
0
ConernestoAuthor Commented:
Hi, attached are two sample workbooks for you to try the code. The goal is to change the contents of cell E91 on the workbooks selected and each workbook should be saved and closed. The tab "Tax Provision" is protected but cell E91 is unprotected. I will be in touch tomorrow. Conernesto.
Book1.xlsm
Book2.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with the tweaked code.
The Sheets in sample files you attached are protected without a password. So the code will no unprotect the sheets, change the cell content and it's formatting, protect it again, save the changes and close the files.

If your actual sheets are protect with a password, provide the password in the line# 20 and 23 inside the double quotes. Also in that case, remember to have the same password for all the sheets.

ws.Unprotect Password:="Your password here"   'Line#20
ws.Protect Password:="Your password here"        'Line#23

Sub Conernesto()
Dim wb As Workbook
Dim ws As Worksheet
Dim file
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
   .Title = "Select The Files!"
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xls*"
   .AllowMultiSelect = True
   .ButtonName = "Confirm"
   If .Show = -1 Then
        For Each file In .SelectedItems
            If file <> ThisWorkbook.FullName Or InStr(file, ThisWorkbook.FullName) = 0 Then
                Set wb = Workbooks.Open(file, False)
                On Error Resume Next
                Set ws = wb.Sheets("Tax Provision")
                On Error GoTo 0
                If Not ws Is Nothing Then
                    ws.Unprotect Password:=""
                    ws.Range("E91").Value = 0.1777
                    ws.Range("E91").NumberFormat = "0.00%"
                    ws.Protect Password:=""
                End If
                wb.Close True
            End If
            Set wb = Nothing
            Set ws = Nothing
        Next file
   Else
      MsgBox "You didn't select any file.", vbExclamation, "File Not Selected!"
      Exit Sub
   End If
End With
Application.ScreenUpdating = True
MsgBox "Task completed successfully.", vbInformation, "Done!"
End Sub

Open in new window

FileWithMacro-v2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ConernestoAuthor Commented:
Hi, thank you for all the hard work you put into this one. I really appreciate this. I did have to enter the passwords and it worked like a charm.

Take care.

Conernesto.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Conernesto! Glad it worked as desired.
Thanks for the feedback.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.