Importing information to a master file from several copy's of the same file

[WEBBERR]

I have a workbook with several worksheets that are all identical in structure and layout representing sales data fed to me from different stores. they all contain 3 distinct blocks of information made from a number of rows (containing different categories of sales) and columns (containing different months), the blocks contain sales purchase costs and anticipated working capital

each month I send out the file to several recipients, they carry out an update for their own sheet (for their store(s)) , and then send me the work book back. I keep the returned files together in the same folder, but change the folder name each month so that i can recall particular months on request.

occasionally new sheets are added and old ones removed. and these represent a single line on the summary page, either added or deleted from the last live list. some of the stores sell more categories than others so the rows are not in the same row numbers on each sheet, but the sheet that is returned is the same as the one sent in each individual case.

i am trying to work out if it is possible to quickly import the information from the returned files, extracting the information that has changed, (only) and updating the worksheet of the same name in the master copy. without having to change the summary page every month to accommodate the fresh/new imported sheets. and any changes to the categories as products are introduced or discontinued

can anyone help with getting me started? I've looked through the past posts and there a few similar items which I have tried as a starting base but I keep getting stuck. I'm quite new to VBA, but have managed to get a few thing working.

thank you for any help and time you can give.

 

[DocAElstein]

Hi WEBBERR,

I have a workbook with several worksheets ......can anyone help with getting me started? I've looked through the past posts and there a few similar items which I have tried as a starting base but I keep getting stuck. I'm quite new to VBA, but have managed to get a few thing working......

_ ......This sounds like a do-able but fairly big project and code. It is along the lines of “Merging Multiple Worksheets or Files to a master file or sheet. As you have said there are a lot of codes to be found outm there along these lines.
But:
_1 ) It is unlikely that you will find a code that does exactly as you wish.
_2 ) You have made a start but got stuck
So:
It sounds like you need specific help rather then getting started which you appear to have already got started.
So to help you we need some specific data to work on.

Ideally we need to see sample data greatly reduced in size . - Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios. The data should show:

_ What you have, that is to say, how everything looks before running of any macro. We call this the “Before” or “Befores”

_ Hand filled in “After” or “Afters” showing how things should look after running of any macro, based on the sample data in the “Before” or “Befores”.

_ You clearly do not have a simple task, and it may be a little too much work for many people working on a free forum to consider. However if you try to produce the information i request you will greatly improve your chance of help, and, as i sometime find, you may find that in organising that info it helps you to solve partly at least your problem.

Hope that is some help.

If you have a go at producing that data , i will certainly take a look and see if i can help. I have answered some similar threads – maybe take a look here and see if there is anything that may be close to what you require:
http://www.mrexcel.com/forum/excel-...through-sheet-copy-contents-new-workbook.html
Copy the data from Master file and paste into individual excel file as per criteria [SOLVED]

Alan

P.s. Some notes on how to supply us with the required info: ( In your case maybe option 3 is needed due to the complexity of the info you have – but again remember to reduce the sample data to make it manageable to work with. )

_ . 1) – post screenshots ( see notes for how to do that in my signature –Please do not post an image as we cannot copy that to a spreadsheet!)
_ . or
_ . 2) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you need to click on the “Go Advanced” Button next to the Reply Button)
_ . or
_ .3 ) – attach a File or Files
_ . For example send over this free thing:
https://app.box.com/signup/personal
( you may need to google around a bit to get at the free version )
_ .Remember to select Share after uploading and give us the link they provide.
_ . or , only as a very last resort please.
_ .4 ). – Private Message me, and I will reply with my E-mail address and you can contact me and attach a File.( To Private Message me: ---You must be logged in. ---Click on my name DocAElstein above the Picture in the left margin. ---Select Private Message. –The rest should be obvious

 

[WEBBERR]

Thanks Alan,

I agree that it is quite a big project. Ive been working on it all day today, but I am getting somewhere with it. I have managed to create a list of file names from those contained in the file folder, and then open them exact the information in them and put it into the master file on the right worksheet and in the right range. which I thought would be the hard bit!

I know that if I use offset and just the "!" instead of prefixing it with the sheet name then excel looks at all sheets, but I haven't yet worked out if I can ignore the first two sheets, starting from sheet3 for example and loop through the rest.

I wonder if a 3D array could do this but i'm not really sure how to construct one of those either. My plan is to add together "F6" on all sheets after sheet3, that are in the work book. this would be the starting point and then i can do the rest from there

I think I'm looking at formula and array structure rather than VBA for this (hopefully the final) bit but that will be in the testing.

Thank you very much for your reply

 

[DocAElstein]

Hi, WEBBERR,
_ Thanks for the feedback.
_ I confess I have very little experience with Excel Formulas. I find VBA miles easier. But that is just a personal preference ( Or a result of my small intelligence! – I can easily read and see what is going on in a code, - but seeing what goes on in some complicated formulas is a bit above me ! ) - : - I am continually amazed what some of the Array ( CSE Type ) Formula experts here at MrExcel can achieve!
_ If you get stuck on a particular formula, it would be wise to start another Thread and ask for specific help on that. What some experts here can achieve with a single CSE type formula never ceases to amaze me.

_ Good luck with your project

_ Alan

P.s.
_ If you start such a new thread you should reference this Thread to keep within the Forum Rules and / or Guidelines. ( - include a link to this Thread - (copy the url from the address bar in your browser) )
_ Also as you develop your project, if you have time, feedback to this thread and someone may step in with some extra help or suggestions. ( And I find Forum Threads that I start a useful extra “Pseudo Cloud” type “back up” for “storing” my developed codes and formulas ! – )

 

[WEBBERR]

I've just been wondering whether a UDF might work, I would put it in a location in the data range, say in cell "C7" and then have the function add up the total of all the C7 cells in the whole book except the sheets where 1) the UDF is used, and 2 and sheet with the sheet name "Summary" although I could use the code name for the sheet instead. I think I will explore that next.

I'm grateful for your replies.

 

[DocAElstein]

OK,
I may be able to help if you have any code problems ( - i think a UDF is in a Public Function type Routine thing , so code stuff )
There is a thing called a Worksheets_Change "Event" Code type thing, - that sets off a code when you type something into a cell, so that is a similar possiblity
Alan

 

[WEBBERR]

thank you, I'll let you know how I get on.