Making the same change to multiple workbooks

Hi,

I would like to know if it is possible to make the same changes across multiple workbooks at once. It is easy if they are in the same workbook, you can just select all tabs and make add a row or make a formatting change to all. I would like for each of the tabs to be there own workbook but still have the ease of making the same change to each. In short is there a function (like the select all tabs within a workbook to make changes to all) for making these changes when they are in separate Files/workbooks. Thank you in advance for any advice you may have.

-Greg

I don't think so....you can do your request in the same workbook but not to reflect on separate workbooks

As vnzerem says no it's not possible.

What is possible is writing a macro that loops through all files in a folder and opends each and every file then changes each file saves and closes the file before moving on to the next file.

Alf

Each file/workbook has only 1 worksheet each. The change I would make (row change or format change) would ideally be made to the 1 worksheet in each workbook in the folder.

Thanks to both of you for responding. Do you know off hand where I may be able to find that macro already written to be used?

I may be able to find that macro already written to be used?

That is not so easy as most macros are "build to order" but a "Loop through file" macro could be used as a base for what you need.

That would be a macro looking like this and you will have to ammed it for your purpose.

Option Explicit

Sub ProcessAllFiles()

    Dim sPath As String
    Dim Wb As Workbook, sFile As String
    
    sPath = "E:\Temp\"
     
    sFile = Dir(sPath & "*.xlsx")
    
    Application.ScreenUpdating = False

    Do While sFile <> ""
         
        Set Wb = Workbooks.Open(sPath & sFile)
         ' your code to modify is added here
        Wb.Close SaveChanges:=True
         
        sFile = Dir
        
    Loop
    
   Application.ScreenUpdating = True
    
End Sub

This macro loops through all ".xlsx" files in the folder "E:\Temp\" something that must be changed to suit your needs. Then there is the question about worksheets, should you loop through all worksheets in each file or do you only need to change specific worksheets?

Alf

Perhaps the easiest way would be for you to record a macro where you start by opening a file do the row change or format cange and then save and close the file. Post the result and I'll do my best to help you incooporare that macro part into the macro I pasted above.

To record a macro you need to go to the "Developer" tab then click "Record Macro" and run through all the steps you wish that your macro should do i.e. open a fille, chang row / format, save file and close file. Then you go back to the "Developers" tab and click "Stop Recording". Then click on Macros. In the macro window that opends click on "Edit" and you will see the macro you recorded in the "Visual Basic" window that opends. Copy macro and post it.

If you don't see a "Developers" tab you need to add it to the "Ribbon"

How to add Developer Tab into Excel 2010 and 2013 Ribbon:
Click the File tab;
Click the Options at the left to enter into Excel Option window;
Click the Customize Ribbon at the left;
At the right, select the Main Tabs from Customize The Ribbon drop down box;
Check the Developer item;
Click the OK button to finish customizing.

Alf

Option Explicit

Sub ProcessAllFiles()

Dim sPath As String
Dim Wb As Workbook, sFile As String

sPath = "C:\Users\gregory.gold\Documents\Macro Test"

sFile = Dir(sPath & "*.xlsx")

Application.ScreenUpdating = False

Do While sFile <> ""

Set Wb = Workbooks.Open(sPath & sFile)

Rows("5:5").Insert Shift:=xlDown
Range("A5").FormulaR1C1 = "4.5"
Range("A6").Select

Wb.Close SaveChanges:=True

sFile = Dir

Loop

Application.ScreenUpdating = True

End Sub


Unfortunately it didn't work. I copied your code over and changed the sPath. "Macro Test" is the folder holding two .xlsx files I am testing this macro out on and a .xlsm where I put the above macro in and ran it. I didn't get any error message but no changes were made to the files.

For the sake of giving you all information in case it may help: The two sample files that need to be changed are called A.xlsx and B.xlsx . Each only has 1 sheet and is still labeled the default "Sheet 1." Maybe there needs to be a section in the macro to apply it to the sheet 1? I could go in and run the section of the macro for the change in each file and that works but eventually there will be hundreds of files.

Thank you

Hi Alf,
I had a very challenging and time killing task today and I did it very rapidly just after following your guidance about "micro".
To be hones, it was my first time using macro.

Thank you so much.

Regards,
AB Khan

Sub Macro2()
'
' Macro2 Macro
'

'
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Range("A5").Select
ActiveCell.FormulaR1C1 = "4.5"
Range("A6").Select
End Sub


There is a simple one for example. I am hoping to use this a strategy for ongoing upkeep of these files so I will take note of how you incorporate the recorded macro into the loop macro. Once I change the file pathway and incorporate recorded macro what is the next step? Do I simply open a new macro-enabled file and insert the final macro as a new macro and run it?

Thanks again for your help.

I've added your part of the code slightly modified as the select command is not needed most of the time but when you recording a macro Excel always add it. Par example the line

Rows("5:5").Select
Selection.Insert Shift:=xlDown

what you wish to do in this case is to insert a new line at row 5 and shift the old row 5 and all following rows down so the suggetion from Excel can be "condensed" to

Rows("5:5").Insert Shift:=xlDown

What still needs to be changed is value of the "sPath". You must change the "E:\Temp\" to the drive and folder where the files you wish to change is found. I've assumed that the files to be changed all have ".xlsx" endings, if they are ".xls" then change the "sFile" part where it says ".xlsx".

Do I simply open a new macro-enabled file and insert the final macro as a new macro and run it?

Yes that is right. A word of warning. Letting a macro loop through a number of files making changes without first testing the macro could be a very bad idea. So I would suggest you make a test folder and copy 3 or 4 of the files you wish to change to that folder. Then run the macro and check if the result is what you wanted.

Option Explicit

Sub ProcessAllFiles()

    Dim sPath As String
    Dim Wb As Workbook, sFile As String
    
    sPath = "E:\Temp\"
     
    sFile = Dir(sPath & "*.xlsx")
    
    Application.ScreenUpdating = False

    Do While sFile <> ""
         
        Set Wb = Workbooks.Open(sPath & sFile)
        
        Rows("5:5").Insert Shift:=xlDown
        Range("A5").FormulaR1C1 = "4.5"
        Range("A6").Select
        
        Wb.Close SaveChanges:=True
         
        sFile = Dir
        
    Loop
    
   Application.ScreenUpdating = True
    
End Sub

Well I guess that is all I can say for the moment. Make your test and if you have problems don't hesitate to post back.

Alf

Great, That worked. Thank you so much for all your help.

You are missing the last "\" in the sPath string, try

sPath = "C:\Users\gregory.gold\Documents\Macro Test\"

instead.

Alf

You are welcome. If this solves your problems don't forget to mark this thread "Solved".
Alf

Hi Abdul,

You are welcome! It's always nice to be of help and being told so as well

Macro is very handy to use when you have repetitive operations and the easiest way to learn is to let excel record the macro for you. Then you can start trimming you macro and test. Does the macro still run properly? Then you trim the macro a bit more.

And don't forget this forum has a huge number of members that do like to solve excel problems so if you get stuck don't hesitate to post your problem.

Regards

Alf

I know this is an old thread, but I'm trying to do something very similar. I'm trying to edit a MASSIVE number of workbooks to include a document number in the workbook and the title of each workbook. I feel like it is very similar to what happened here. Can anyone help me? I'm on excel 2016 but I honestly feel like it can be done the same way but I'm not getting it right

Thanks so much, this just saved me so much time!

Hello Mr. Alf, when I search google for my need I have got this post. My query also same like this only. Actually I had 1000's of workbooks with same worksheet names. The thing I need to do is in all files on the specific work sheet I need enter a data. Next thing I need to keep the format of on the specific worksheet. Can you please help on this.

Hi a latecomer to this post, but I found it very helpful....It amends my 3 files in the folder, except when I change a cell after that so in this case, then it no longer updates the other documents, unless I make the change in the Macro itself.

Also it gives a runtime error, can you help please?