Making the same change to multiple files

[basicallyawesome]

Hi, I did search before posting and saw Cindy Ellis’s answer on this thread, but do feel my situation is different enough to warrant asking another question.

Employees and hired contractors get paid a specific fees for two services. These fees are in two separate cells on each of their invoices which are creating using excel. Each person has a separate excel workbook (resulting in over 800 workbooks, not ideal, but we’re now in a time crunch and can’t fix it). The first tab of each workbook, the one that needs to be updated is labeled with the persons name, again due to the volume this would be difficult to change at this stage in the game. All 800+ sheets need to be updated with the new fees for the 2023/24 year. To make matters worse, some have been merged into master workbooks so in some workbooks there is more than one sheet that needs to be updated.

is there away to overcome these challenges and update one sheet and have the rest update using VBA? Even if this can’t be completely automated, is there a way to use one sheet as a master sheet and have the rest update off of them (could I use VBA to insert the cell references to the master sheet on opening?)

Thank you!

 

[Micron]

Yes, it should be possible to loop over all the wb's in a file folder, open each one, count the sheets, if 1 change 1 if more change more. All cells to be changed would have to be in the same place and I think, use the same values. Then save and close one by one. If the subfolders have to be navigated, that adds to the complexity. If it were me I'd try to convince my boss that using Excel this way is madness, and this should be proof of why. Time for a database.

You could probably assemble a team and split up the workbooks and edit them all before you could code this.