I have multiple worksheets (Monthly timesheets) with lots of formulas. so if we need to change a formula we need to update all tabs individually which take quite some time. is there a way that I can create a "master" sheet which I can update and the worksheets will update automatically? I attach some screen dumps
Hi Roma1r
Screen dumps fulfill no purpose. Ideally, in order for someone to assist, we would appreciate a sample file upload.
Attach a sample workbook . Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Hope this helps:-)
Not really....Explain what you want to achieve...
It is my understanding that you have a Main Folder with SubFolders and within each subfolder is worksheets....
You want a master sheet which you can make use of to update formulas in specific cells and with code go and update that exact cells formula within each and every file within each and every Subfolder....Correct?
So what does your upload provide?
Okay sorry I'm not explaining myself so well - example I had to change the formula (=ROUND(IF(C7>E7,((E7+0.5)-(C7-0.5))*24,((E7-C7)*24)),1) in sheet a G7 this morning (G7 to G67) so that formula now needs to be copied to all workbooks/sheets. I was hoping to make such changes only in a "master" sheet and update all other sheets from this "master" sheet
Okay, so....
If in your Master you changed the formulas for range("G7:G67")
You now need to Change that exact ranges formulas in every sheet in every file in every folder....
Is that correct?
Correct:-(
K, this works provided you have a Main Folder housing your master and then Subfolders housing the individual sheets.
So how I tested was as follows...
I changed formulas in a range call it D5:D15
I then selected the range and ran code.....
the code copied that selected range to every file within each folder at the exact same place...
will only work if all files are identical...
Please Login or Register to view this content.
okay thanks - might take a while before I start with this "project" but is it possible you can send me an example of what/how you tested?
Extract and put on your desktop
Update formulas in master file highlighted area, then select the area and run the code...
Good day - I have now managed to create Master plus secondary (receiving workbook/s)
I have 2 more questions pls
1) when running the code with updated data in the master file it only updates the 1st sheet in the receiving template unless I group the multiple sheets together, is there and auto way of updating all tabs in the receiving workbook?
2) if the receiving tab is password protected is there a code that can be added to the above to auto unprotect, update data and protect again?
I'm attaching some workbooks
many thanks in advance
This should solve...
Please Login or Register to view this content.
Good day
We have created receiving sheets and the macro works fine, thank you. the only problem we have isthat we need to exclude certain sheet from the update. in this case only the sheets with names inthe tabs need update and not the others, is it possible? There might be times that we need to update some of the "no names" tabs but for now its not that important but it would be helpful to include/exclude some of the sheets when necessary
I'm attaching the workbook/master template
Many thanks in advance
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks