Excel macro help (intermediate/advanced excel knowledge required)

[Ns1]

Issue-

We have a team of 8 users who are required to keep individual log files.

I need-

A macro that will consolidate all 8 sheets into 1

How hard would that be to do? I have zero actual VBA/macro experience but I learn quickly. I taught myself basic SQL recently, and I have a little CS experience (I passed a java class some 10 years ago LOL, CS dropout here).

SO can someone point me in the right direction?

 

[Ns1]

Would this post be better served in programming since it involves macros/vba?

 

[PowerEngineer]

Well, let's see if I can.

I'm assuming that members of the team are actually keeping (and saving) their logs (on single worksheets) in separate Excel workbooks (rather than on separate worksheets in the same workbook).

If you want to "consolidate" these individual workbooks into a single workbook, then the easiest approach is to create a new "consolidated" workbook into which you copy the log worksheets from each of the team members' saved workbooks (i.e. open member file, copy worksheet, paste worksheet into "consolidated" workbook, close member file...).

If you want to actually concatenate the eight logs into a single worksheet, that will be more difficult as you'll need to be able to determine the length (i.e. number of rows) in each log in order to right-size the copy of each log and to identify where to start the paste into the consolidated sheet (for the next log).

If the log entries are time stamped and you want them ordered by time, then sorting of the consolidated sheet will be required.

One way to "jump start" your VBA macro learning is to use Excel's "record macro" capability. After you start it up, you perform the actions manually. It will write macro commands that implement your manual actions. I'll warn you that the results are the crappiest programming you'll ever see (not unusual for the recorder to use five lines of VB where one is needed), but it's a start.

Good luck.

 

[ringtail]

I routinely merge the separate workbooks from approx. 200 people all over the world into one summary worksheet with the free Ron de Bruin merge add-in for Excel from here.

For the VBA code behind the merge add-in, MassiveDynamics discusses it here.

The merge add in works great, but only thing is that it’s slow. It’s all set up with a nice GUI and selectable options. For me, the horizontal display option gives a better result than the vertical.

 

[Ns1]

I routinely merge the separate workbooks from approx. 200 people all over the world into one summary worksheet with the free Ron de Bruin merge add-in for Excel from here.

For the VBA code behind the merge add-in, MassiveDynamics discusses it here.

The merge add in works great, but only thing is that it’s slow. It’s all set up with a nice GUI and selectable options. For me, the horizontal display option gives a better result than the vertical.

holy shit, infinite greens for you.

txt

Thanks PE for the reply, but I think it was superceded by ringtail's reply!