Importing data from multiple workbooks into a summary workbook

Hello to you all!

I'm looking for some help. I've searched the forums and found several questions similar to mine but I have no idea how to apply the suggestions to what I am doing.
Please keep in mind that I do not (yet) know anything really about macros, VBA, et al.

I will have several workbooks (about 20) that I want to import in one summary. As there are headers in each workbook I don't want that information imported.

I've attached the rough draft. If anyone could help I would be ever grateful!

Also, is there a way for it to figure out that nothing was entered on a row and NOT import that data? If not, how do you write a macro to sort.

Thank you so much for any help!!

Originally Posted by Joaniee

Hello to you all!

I'm looking for some help. I've searched the forums and found several questions similar to mine but I have no idea how to apply the suggestions to what I am doing.
Please keep in mind that I do not (yet) know anything really about macros, VBA, et al.

I will have several workbooks (about 20) that I want to import in one summary. As there are headers in each workbook I don't want that information imported.

I've attached the rough draft. If anyone could help I would be ever grateful!

Also, is there a way for it to figure out that nothing was entered on a row and NOT import that data? If not, how do you write a macro to sort.

Thank you so much for any help!!

In essence you need to perform the following steps starting the macro from within the summary workbook. They will need placing in a loop which accesses each of the tracking logs.

1. Open each of the workbooks.
2. Copy the relevant non zero rows
3. Jump back to the summary workbook and paste the data in the next available cell.

You might also want to consider how you handle the situation where someone wants to resubmit a log that you've already included in the summary. Will you allow this in which case you'll need additional code to delete any relevant rows? In this event you'll probably need to have an extra column in the summary sheet which holds a reference to the tracking log that supplied the data.

Question. How are you determining that a row is empty. Is it a sufficient test that column A is empty for instance? And then is it possible that you can have gaps in the rows? i.e. might there be data in say rows 4,5,7,9, but not in 6 and 8? If the latter is true then you'll probably need an initial sort to make sure that there are no gaps.

Selecting the rows is pretty straight forward since you'll select

Please Login or Register  to view this content.

The other thing to consider is how are you going to determine which workbooks are the tracking log workbooks. There are two options.

1. Put them all in one folder, and only have tracking logs in that folder, then have the macro open every file in that folder.

2. The other and perhaps safest way is to ensure that the names all have some common text which is unique to the tracking log books. Then the macro can test for the presence of that text before opening the workbook.

If you can answer the Qs above, then post back and no doubt we can work out some fairly simple VBA code.

Rgds

I'm in the process of making some changes to the originally posted workbooks, minor, but I will repost then soon.

to answer your questions,

a row would be considered empty if there is nothing in column A. (Is there a way to make that required for each row that is typed upon?) I would auto fill it with the dates but there is a chance that there would be multiple entries on one day.
The sort would more than likely be column L then column B.

I'm not sure what you mean about which will be the tracking workbooks. Each person will work with their own workbook, and the tracking will go to the supervisor. (If at all possible I would prefer to have the summary workbook in a seperate folder that only supervisors have rights to.)
The other files, I would rather have coded than using the name of the individuals, that way if someone leaves the organization, a different person could in effect step in and use that file name... example, 01monthlylog.xls, 02monthlylog.xls, etc. And they would be centrally located on the server. I'm not 100% on how the security is going to be.. and I know that will be an issue.


If you have further questions (or ideas!) please let me know. And thank you in advance for your help!

Ok, I was able to perform a macro doing just what you said, and it worked fine, except that the files had to be open. Is there a way to run the macro without having to open all the files?

Originally Posted by Joaniee

I'm in the process of making some changes to the originally posted workbooks, minor, but I will repost then soon.

to answer your questions,

a row would be considered empty if there is nothing in column A. (Is there a way to make that required for each row that is typed upon?) I would auto fill it with the dates but there is a chance that there would be multiple entries on one day.
The sort would more than likely be column L then column B.

I'm not sure what you mean about which will be the tracking workbooks. Each person will work with their own workbook, and the tracking will go to the supervisor. (If at all possible I would prefer to have the summary workbook in a seperate folder that only supervisors have rights to.)
The other files, I would rather have coded than using the name of the individuals, that way if someone leaves the organization, a different person could in effect step in and use that file name... example, 01monthlylog.xls, 02monthlylog.xls, etc. And they would be centrally located on the server. I'm not 100% on how the security is going to be.. and I know that will be an issue.


If you have further questions (or ideas!) please let me know. And thank you in advance for your help!

OK. To Summarise

1. Is a sort mandatory on the columns you indicate.
2. Is there a requirement to reload an individuals workbook if a change has been made?
3. Is your first question that each cell on each row should be checked whne defining whether a row should be included, or just the first cell in Col A? Each row would of course be checked.

By the tracking workbooks I just meant the various individual's workbooks.

Rgds

I was able (with your help) to run a macro that did what I wanted it to do. The summary workbook would be used thoughout the month and so yes, it would have to reload. If there's a way around that, yeah! I figured the summary would either need to be renamed after data was imported, or not saved at all. I'm sure there's a work around so it would load only the new data though?

The row would need to be included in the summary if a date is entered. This being if the surveyer was sick. All fields would be empty except column AN.

Thanks again!

I looked at your questions again and realize that I did not really answer them.

The sort would not truly be mandatory to run. The supervisor could just as easily run her own sort.

The reloading of the workbooks would be done any time the supervisor opened the summary workbook. I realized after testing it, that if you save the summary page, it saves the data and will want to pull it back in if you run the macro again. The preferable way is to have it look for and add new data each time it's opened.

Ideally, the end result would show only rows that have something entered upon, and all the blank rows would be deleted.
Giving the supervisor only data, and a grand total.

Once again, thank you for your help!