VBA script for collation of data

**Sushesh** · 05-09-2008, 12:35 AM

Hi guys :
I've been working on a requirement that involves, collating data from multiple sales reps and consolidating them into one file. Once this collation is done, we would be using this worksheet to generate reports . This is required on a monthly basis
I managed to find some code on the web , that helps me collate the data, but need help in these two aspects :
1) I'm able to collate the data from multiple sheets (it is appended in the same worksheet), but i loose the header in the master sheet. The header will be in the same lines as
Sl No Sales Rep Customer Product Month Unit Price Qty
How can i ensure that the master has this header and the collation of data starts after their headers.

2) On running of the macro, all files open up and remains open after the data is collected on the master. How can i close the sheets after the master has been populated with data.

3) Since the data is collected from multiple sheets, each users data have their line items numbered, but in the master sheet, we find that the numbering (Sl no) is not consistnt. How can i have a sequential numbering in column A for all data.

Would appreciate any inputs , pointers in this regard...

TIA.

Sushesh

The code that i use is :

Please Login or Register  to view this content.

**royUK** · 05-09-2008, 02:21 AM

The example code that you have used copies data from several workbooks to one. It starts by clearing the data that exists in the master sheet, leaving header rows, it then copies the data without header rows. So if you have no data in the sheet the first copy must import the header rows. Do this by checking for data in the master sheet & setting a Boolean variable. The following cod should work but I haven't tested it.

Please Login or Register  to view this content.

Your second question should be in a separate thread! However, if you use a formula to set the sequential numbering in each workbook based on the ROW() function, this would update when the new workbook calculates.

**Sushesh** · 05-09-2008, 03:37 AM

Roy,
I tried this out, I use a blank excel worksheet, add the code in VBA. When i run the macro, the data gets collated.

How do i set that the data should start getting appended , from row 3 or 4. with row 2 being the headers.

2) This problem still persists,all files are opened and data is copied into the master document. After the copying, the files remain open.. If the number of documents are more, then it may cause a problem. Can you help me tweak the code so that it closes the file after it copies the data ?

3) On the row numbers, i will work on it and confirm.

I really appreciate your time on this ! Thanks,
Sushesh

**royUK** · 05-09-2008, 03:47 AM

Please Login or Register  to view this content.

**Sushesh** · 05-09-2008, 04:44 AM

Roy,
It worked great !!
Headers get copied onto the master and if there is a header then just the data gets copied. Also, the files are closed after the import of data.

One last thing.. there is a blank row between the data from different sheets.. Could you help me out removing this and making the data range in the master continuous ?

Thanks !!

Sushesh

**royUK** · 05-09-2008, 05:54 AM

If you try to follow the code you will see that this line skips a row

Please Login or Register  to view this content.

So change it to

Please Login or Register  to view this content.

**Sushesh** · 05-09-2008, 06:19 AM

Thanks a ton for your time !!

This worked.. Glad u put up with my questions... I'm a person who understands the logic, but not VBA syntax.. guess should start learning this now

Sushesh

**Sushesh** · 09-08-2008, 01:53 AM

Hi ,

Since the last two weeks, i've been working on some advanced reporting after the collation of data and realised that the above module does not collate data from all the sfiles... Infact, it collates data from n-1 files. (n being the total files in the folder)

To test this out, i put 2 files in the folder and ran the macro, and it got data only one file. When i put only 1 file in the folder, it did not get any data.

Please help !!

Sushesh

**royUK** · 09-08-2008, 03:50 AM

I can't see why it should be missing any workbooks, it checks for files in the directory then simply loops through each one. Check how many files are being found by running it with this extra line

Please Login or Register  to view this content.

**Sushesh** · 09-08-2008, 05:19 AM

Roy,
I just checked this, it displays '2' but gets the data only from the first file. To cross check, i added another file, it displayed '3',but it picked up data from 2 files.

Don't know wat i'm doing wrong.

Sushesh

**royUK** · 09-08-2008, 05:44 AM

Are you syre that all data is in the same sheet & range for each workbook?

**Sushesh** · 09-08-2008, 06:02 AM

Yes, the ranges /sheets are the same. I've compiled 4 files so that i can share the same with you so that it would be easier, but i've not been able to uplaod them onto the forum due to the size. I have 4 files within the archive with a size of 174kb

Any suggestions ?

Another thing i've noticed is that whenever i try to compile data from 3 files, the data comes in from 2 files, with the headers..

Sushesh

**royUK** · 09-08-2008, 06:09 AM

You can zip the4 files together then use www.yousendit.com, post the download link here

**Sushesh** · 09-08-2008, 06:13 AM

http://www.yousendit.com/download/bV...R3NCMTZGa1E9PQ

Master file has the macro. a,b,c are the input files

**royUK** · 09-08-2008, 08:13 AM

I cannot open any of these files .

**Sushesh** · 09-08-2008, 08:25 AM

hmm.. can't see where the problem is , just downloaded the zip file from the link posted up and i was able to open all the files..

Sushesh

**Sushesh** · 09-08-2008, 08:35 AM

Let me put the contents of the three files here..all files start from A2.
file a.xls
S.No Sales Team Customer Name Part Sept
1 Maruth ABC 23 7000
2 Maruth ABC 22 2000
3 Maruth AC 42 20000
4 Maruth AD 43 20000
5 Maruth FD 55 50000
6 Maruth GG 22 100000
7 Maruth GS 55 25000
8 Maruth BB 44 7000
9 Maruth BB 52 2000
10 Maruth SS 45 20000
11 Maruth NK 33 20000
12 Maruth NK 22 50
13 Maruth NK 44 50
14 Maruth NK 55 7000
15 Maruth NK 55 2000
16 Maruth NK 55 20000
17 Maruth NK 55 20000

file b.xls
S.No Sales Team Customer Name Part Sept
1 Pawan ABC 23 7000
2 Pawan ABC 22 2000
3 Pawan AC 42 20000
4 Pawan AD 43 20000
5 Pawan FD 55 50000
6 Pawan GG 22 100000
7 Pawan GS 55 25000
8 Pawan BB 44 7000
9 Pawan BB 52 2000
10 Pawan SS 45 20000
11 Pawan NK 33 20000
12 Pawan NK 22 50
13 Pawan NK 44 50
14 Pawan NK 55 7000
15 Pawan NK 55 2000
16 Pawan NK 55 20000
17 Pawan NK 55 20000

file c.xls
S.No Sales Team Customer Name Part Sept
1 John ABC 23 20000
2 John ABC 22 20000
3 John AC 42 50000
4 John AD 43 100000
5 John FD 55 50000
6 John GG 22 100000
7 John GS 55 50000
8 John BB 44 100000
9 John BB 52 50000
10 John SS 45 100000
11 John NK 33 50000
12 John NK 22 100000
13 John NK 44 50000
14 John NK 55 100000
15 John NK 55 50000
16 John NK 55 100000
17 John NK 55 50000

and the code that is used in the master sheet..

Please Login or Register  to view this content.

**Sushesh** · 09-09-2008, 05:39 AM

Hi roy,
Did you find the data sufficient to work on ?

Thanks,
Sushesh

**royUK** · 09-09-2008, 05:44 AM

I'm looking at it now

**royUK** · 09-09-2008, 08:34 AM

The code works exactly as I expect. You haven't placed the master workbook in the same folder as the data workbooks?

This is the code that I have ended up with

Please Login or Register  to view this content.

**Sushesh** · 09-09-2008, 09:04 AM

Roy,
Previously i was placing the input files in C:\test\ and the master file on the desktop. It worked fine other than the problems that i had mentioned.

Today with the code u just posted up, when i run the macro, it just freezes the file and after a few mins, it throws up a blank file. (this is with the master and the input files in the same folder)

When i tried the same with the master file on the desktop and the input files in a diff folder, it did freeze for a few mins but it collated the data from all three files, but with the headers repeated thrice. So one problem solved,ie data gets collated from all 'n' files.

any specific changes u made to the macro ?

Anything that i'm doing wrong ?

Sushesh

**royUK** · 09-09-2008, 09:08 AM

The master file should not be in the same file as the data files.

The code that I posted last copies all the data for me with only one header row.

I couldn't open your files so I created my own, just 3 files with the same layout containing different data.

**matenginerd** · 10-23-2008, 09:30 AM

I am relatively new to writing macros, and have been developing a macro to collate some data sets. In searching for people working on similar macros, I came across your proposed solution above, and have had some success modifying it to overcome the trivial issues, unique to my data set (i.e. 2 row header, etc.).

What I am looking to achieve with the macro I'm working on is to only copy particular columns from each data set, and I have been having some problems. When modifying the code provided above to copy a defined range (i.e. Range("B:C,G:G,I:I,L:O"), as opposed to all the data (usedrange), I am finding the macro only copies data from the first file. The macro loops through all of the available files, but does not copy anything beyond the first set. I'm not sure if there is something inherent in the "usedrange" property that makes this macro work, or if I'm just overlooking another issue. I have exhausted my knowledge of macros to this point, and decided to come back to the source to see if you had any expertise/suggestions you could share.

Is modifying this macro the way to go, or should I be starting from a different direction!?