Change to multiple worksheets in same file?

Hi people!!

As the title says really, is it possible to make the same change to multiple (hundreds) files in the same folder at once, without opening them all and changing them individually??
For example i have hundreds of files and they all have a database tab and i need to update the database but obviously changing them all individually would take a while!!

So is it possible to change certain info on a certain tab in multiple files at once i suppose is the question??

Thanks in advance for any help!!!

Maybe you can adapt this:
http://www.mrexcel.com/forum/excel-q...workbooks.html

Thanks for the response!!

That didn't do what i'm after!!

I don't wanna move any data just change data (the same data in each file) that is already in each file (all 1015 of them)???

I'm assuming it can not be done but thought i would ask the question......

I think by changing data in every files like your question without open files is not possible. But by using VBA you will able to change data in these files by just one click. However VBA will open those files and close them after updated.

If you would like to know how to achieve it pls give me few sample file then i will write VBA code and post back to you...

If anyone know other way better than mind pls share, I also would like to know it.

So if i wanted to changed cell A10 in the 5 attached files from 'AMPLIFIER GUITAR' TO 'GUITAR AMPLIFIER' for example, there is a code that will do this??

There will need to be things added to the list in the future! For example add 'BANANA' to cell A17 and 'CAMERA' to cell A18......

Thanks for all your help!!

Well it's something that is being constantly added to! Previously i have updated each sheet individually (very time consuming) as and when a new product is needed, so if there was one hidden sheet that i could update and that will update all worksheets then that would be great?? Is that really possible??

I've attached a file with a hidden sheet but i'm not really sure what i'm supposed to put on that hidden sheet?????

Oh, that sounds exactly what i need!! Would be great if you could do that??

Thanks again for all your help!!!

If you could use dummy data please!! Obviously the files i provided were just for example. I will incorporate your code into my actual files once received....

Thanks!!

Could you just use dummy data please! I will import your code into my file once created....

Field text is column A and replace text is column A also....

Thanks!!

Yes, all in 1 line in column A! The list is about 500 lines long if that matters...

Thanks for doing that!!

When i run the code i get the following error massage;

Run-time error '52':
Bad file name or number

Do i need to change something within the code perhaps??

It's highlighting the following;

CurrentFileName = Dir(myPath & "\*.xls")

I have changed the line you suggested but still get the same error and it still highlights the above line??

Currentfilename=""

I've tried again and this time it didn't throw up any errors!!! But, it doesn't seem to have done anything, is there a command i need to give to change all the files???

Last message wasn't very clear, Let me re-phrase - Is there a command i need to give to make 'changes to the multiple worksheets' within that same folder, once the code is running???

I have!! The screen flashes then does nothing to be honest! I'm using a files with the examples i previously attached in this thread....

I've attached the first file from my test folder (with 7 different files all with the same info) which has the code in, just in case.....

Cheers
FrazzFreeman

All the files within the folder contain the same text on a specific tab which is labelled Database.

Some background;

This Database is a list of electrical items, when new items become available i need to add them to the existing database's (in my actual folder i have approx 500 files, so to change each file would be very time consuming).
Also some existing text needs to be changed on all files, like the example i have given previously e.g. AMPLIFIER GUITAR TO GUITAR AMPLIFIER.

Hopefully this clears it up and it is still possible to do??? Apologies for not being clearer...

No, the changes will be the same for each file but there could be many changes!!

To clarify, i would need to make a list of the changes that i need in the 'Database' tab of the sheet that is running the macro so it can pull the information for all files from there??

What if i wanted to add additional things (not changes as such, but new data) to the original list, would that be a separate list on the same tab (Database)??

I will try the code now and let you know....

I have ran the macro and the screen flashes again and appears to do nothing??

I've attached the first 2 files from my folder (TEST 1), the file 1.xls has the macro and i have made some changes to the list for test purposes, you'll see that file 2.xls has no changes???

That would be easier!!

(As previously mentioned there are entirely new entries that need to be added too!! I don't know whether that could be column C??)

Ok, do you have all information from me for the code now???

Thanks again for all your help!!!

Good news, the changes now work!!! Thanks for that!!

The additions do not though, I assume once again I'm doing something wrong but not sure???

(I've attached the macro file and one of the files that should have the additions)

Perfect!! 1 last thing though, can you add to the code to make it sort column A into alphabetical order once the changes have been made??

Right then, this code is perfect when i use it on my test data, however, I have added one of my actual files into the test folder and it does not make the changes!
The only difference between files that i can see is the tab number (the test data 'test1' is on tab 1 and my actual file 'test2' is on tab 10), although they are both labelled 'Database'. Is that the issue?

Yes they are!!

Some files pop up with an error 'run-time error - 1004. The debug highlights the following...

Please Login or Register  to view this content.

Would it be because there are empty cells on row 2??

All the files have headers in other rows!! So, which part of the code do i need to change specifically???

The other header is in cell D1 and again row 2 is blank??

That's correct!!!

Amazing!!!

Looks all good now!! Thanks again for all your help!!!

I'm sure we'll speak again, haha!!

Thanks!!!

Unfortunately, another issue has arrived!!

Everything seems to be happening fine other than, the additional items seem to have copied three times instead of just the one???

See the attached, i've marked them in red!!

Help??

I'm pretty sure it was after 1 run only!!!!

Thanks! I'm out the office until Monday but I'll try it then and let you know!!!

Again, thanks for your help.....

Again, i think it's now working!!

I'll marked the thread solved!!

Thanks again for all your help!!!!

Regards