VBA - auto save / update

Epicfail

Board Regular
Joined
Mar 31, 2009
Messages
89
Hi and thanks for looking at my issues.

Ok i have a workbook that pulls data from 7 other workbooks based off of 3 criteria. In 6 of the 7 workbooks there are 6 sheets and in the last sheet there are 15sheet. All the sheets are used on different computers and currently the member who use these sheets cant remember to save them so the main workbook can pull the information. So I was hoping for a VB code to do the save for them after they enter any data in the range of A1:J50.

Then for the main work book i was hoping for a VB code to auto update after entering the 3 criteria. I have a marco made but it takes awhile to do it. First it saves the sheet, then pulls the information, then refreshes some pivot table, then populates into the proper cells.

If these codes cant be done any other ideas

Hope I have placed enough data here,

Epicfail
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Then for the main work book i was hoping for a VB code to auto update after entering the 3 criteria. I have a marco made but it takes awhile to do it. First it saves the sheet, then pulls the information, then refreshes some pivot table, then populates into the proper cells.


Is there a way to make vb code so if someone changes a tab name it change it in the formula's on the main workbook w/out haveing bothe sheets open on the same computer?
 
Upvote 0
ok first question, paste into the sheet that you need it to work in, not a module

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
activeworkbook.save
end if 
end sub
 
Upvote 0
Hey Epicfail,

as for your other questions yes I have seen something that may work, but it may bugger it up at the same time, using a referance to an actual sheet number then getting the name of that and using it in the formulae via macro could work... big down fall if someone adds a sheet this will fall over.

One suggestion lock the work book down no adding sheets and only update the cells you want users to work on. Play around with protection and cell formatting (far right tab) should help you control the end user how you would like.

This sounds like a big lot of work... have you thought about starting from square one again may be easier to look at the whole lot and find a simpler solution rather than fix what you have now.
 
Upvote 0
Yeah I have the sheets protected( so they can only enter information in certain cells in the a1:J50 range), but as for them adding a sheet it wouldnt hv the template for theyre information. Origanlly this was made to run a total of three sheets on one computer. But the other leadership members wanted one computer per line so i had to break it out:mad:.
 
Upvote 0
Oh it works now, but im trying to make it easier for the member to use it.

I am interested in what you have seen, because it might work. The members using this dont know that much about excel so I keep the sheets very limited to what they can do / access.
 
Upvote 0
Hey Epic,

When you get the chance send me more details, i.e. one computer per line??? can you post the sheet or email it to me?
 
Upvote 0
Oh by the way i use excel 07, and we can run up to 6 line so i needed one sheet for each line.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top