Automatically Update Data on VBA Code

[Jacolene]

Hi,

Please advise if it is possible that a VBA code automatically update the data if there is change made?

I am using the below code to add 38 sheets of data in the same workbook, to 1 sheet (also in the same work book)

But if I make changes to data on a sheet, the combined sheet, does not update automatically, I have to run the code again

So I need to know if there is a way that it updates automatically?

Please Login or Register  to view this content.

[gmr4evr1]

If you only need it to run from 1 particular sheet, change the Sub name to

Please Login or Register  to view this content.

and place it in the code window for the sheet you want it to run when a change is made.

[Jacolene]

Hi, I need it to take data from 38 different worksheets (in the same workbook), not only 1

[gmr4evr1]

Will the code you posted do it for the 38 worksheets?

[Jacolene]

The code posted, will combine all 38 sheets into 1 called "combined"

[gmr4evr1]

k, Try this instead. Change the Sub name to

Please Login or Register  to view this content.

and place it with the code in the ThisWorkbook module.
This will run the code when a change is made to any sheet.

[Jacolene]

Thanks, but I am not sure where you want me to insert it?

[gmr4evr1]

This will be your code with the new name..

Please Login or Register  to view this content.

With the VBA editor window open, on the left side where the Sheet names are listed, you will see one that says ThisWorkbook. Double click on ThisWorkbook and paste the code into the editor window on the right.

[Jacolene]

I did as you said, but it is not doing anything, not even creating the combined tab

Please see attached a sample of the workbook

[gmr4evr1]

Is it possible you sent the wrong file? The changes I suggested were not there, but, now that I see what you are working with, I'm not sure my suggestion will work for you. Might be better off creating a button that runs the macro.
Also, the sample you attached already has a tab named "Combined" therefore it will not create another one named "Combined" because you cannot have 2 sheets with the same name in the same workbook. You may have to add an If statement for the "Combined" sheet...one that says that if the Combined sheet exists then do this else do that.

[Jacolene]

Hi, it is the correct file, and I have already created the tab "combined", all I want the code to do is to update the "combined" tab, if any changes are being made in any of the "wards" tabs

[gmr4evr1]

Ok, I think I have a better understanding of what you want now. I will modify the code so that it doesn't try to create the combined tab since once is already there.
I've been tying to think of a way to have the Combined sheet update when the other sheets are changed. I don't think this would be the best approach because the code would run each and every time a change was made to any/all of the sheets which would cause lags and such. I do think I have an idea though. I'm going to place the code in a Worksheet Activate event so that when you click the Combine tab, the code will run then, and only then to update the sheet.
Edit* Will the Combined sheet always be there????

[gmr4evr1]

If the combined sheet will always be there the slightly modified code in this updated workbook will work..
Jacolene-automatically-update-data-on-vba-code-sample-copy.xlsm

[Jacolene]

Thank you for your assistance, I will give it a try and let you know