VBA - auto save / update

[Epicfail]

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

 

[Epicfail]

I sent you my sheets, prolly gonna take you a bit but i also sent u a run down of how it works along with it.

 

[Ed in Aus]

Wait do you mean you can run up to 6 produciton lines? I was thinking it had something to do with Excel 07

 

[Epicfail]

Yes we could run up to 6 production lines, each with there own sheet. lol
Thats why there is 6 time sheet workbooks(WB) with 6 worksheets in each WB.
6 WB in each File Folder and one file folder per shift. hehehehehehe

 

[Ed in Aus]

ok sounds like you company likes the number 6... not working for um that guy in a red suit by any chance.

let you know if I can come up with anything if not I will be sending you back here

 

[Epicfail]

If I was working for the guy in the red suit he could give me the know how to do this lol...i think

 

[Ed in Aus]

Ok got something about referring the actual sheets (No.) / name

Sub test()
MsgBox Sheet1.Name
Sheet1.Name = "Blue"
MsgBox Sheet1.Name
MsgBox Sheet2.Name
MsgBox Sheet3.Name
End Sub

now this is the hard part, what needs to happen is you need to basically ask if a sheet or sheets have been added then what are their names correct?

Sounds like an easy question... but not too code... any one help with coding this one???

I was thinking something like this... to get the sheets and there names and maybe an if statement against this to re do the formulas.

something like a if the sheets aren't sheet1,sheet2,3,4,5,6 etc then rename the formula's

Dim i As Worksheet
For Each i In ActiveWorkbook.Worksheets
MsgBox i.Name
Next i

 

[micro-blades]

I don't know if this applies to what you are doing or not, I thought I would throw it out there in case it may simplify your life a bit.

I too have a main workbook that it is necessary for 4+ people to enter data, often at the same time. Those folks are also very limited in knowledge of excel. To get around issues of file sharing, read only access and inexperience I do this:

I have 1 readonly workbook that acts as a console via a userform, it is opened using a .vbs file. When the .vbs file opens the workbook, excel is hidden and ONLY the userform is seen. The user enters the data, clicks a button and the data is saved in a text file. All the users have access at anytime and leave the form open pretty much continously. The user has absolutely no access to excel other than the form, therefore nothing can be 'accidentally' changed in the workbook.

The main workbook contains a simple import macro plus a multitude of pivot tables and formulae. On open (if it's not opened readonly by a second person), it automatically imports the text file data and deletes the files. No ties to other sheets/workbooks, no inexperienced people mucking around or adding/deleting sheets, no problems whatsoever.


I have used this same procedure a few times when one workbook needs data from several people at often the same time. I find myself relaying data via text files more often, it's very quick and efficient, especially in a production reporting atmosphere.

Food for thought...

Brian

 

[Epicfail]

I dont know what a userform is but I have the save and update links done but it doesnt change the sheet names. Any more ideas?