VB help on work project

[thelazyturttle]

Hello Everyone,

I'll let you know I'm in accounting. I don't know a significant amount about VB but i've done a few projects that have at least given me a bit to work with. I need help with 1 part of my current project. Every Quarter I receive dozens of spreadsheets that I need to analyze.

• Each spreadsheet has the following data points shown below.
• Each spreadsheet has a random amount of people. Some 2-3 people, some with 1-2 thousand people.

LName FName MI SSN PSDate PPDate PD $A $B $C $D E <tbody> </tbody>

<tbody>
</tbody>

The ideal outcome would be as follows.

1. Combine all spreadsheets into one workbook. (I have a working macro that combines them all by making each file it's own sheet) So my current result is one excel workbook, with roughly 28 sheets.
2. I need a new sheet that has 1 row dedicated to every account possible within every single one of those 28 sheets. I was thinking maybe pull the first 4 columns of every sheet and add them into a new sheet. Then remove duplicates? Since I have the SSN, I always have a unique identifier?
3. Then I need to have the sum of $A, $B and then E. So my result would look like this.

SSN

LName

FName

$A

$B

E

<tbody>
</tbody>

1. Basically, if Bob shows up in 3 sheets with 500 in $A, 50 for $B and 20 for E. It would look like this.

Smith

Bob

111-11-1111

1500

150

60

<tbody>
</tbody>

Sorry if this is a lot. I'm trying to learn VB but I can't seem to combine the sheets into one while simultaneously removing duplicates and summing the values of each sheet's amounts for those specific columns.

Thank you for any help,
Turttle

 

[thelazyturttle]

Sorry for the formatting, I'm typing this up at work and it's chaotic today.

Thanks in advance for dealing with this cluster of a question