SO close, yet so far -Macro to identify info between 2 sheets and paste/loop on condition-

Thank you in advance for your help. I have two worksheets, one with census data (Sheet1) and another with account balance data (Table 1). I'm trying to come up with a code that transfers the individuals account balances between the sheets.

I'm able to get the code to match up names with the chart, check which account source ("Profit Sharing" in this example) and to bring the data where I want it. However, the only problem is that it pastes every iteration of every persons balance in each cell where they need to go, ultimately with the last balance pasted uniformly into Sheet1.

For example:

John: pastes Johns, Bills, Dillans, then pastes Jerry's Profit Sharing data in cell cell P1
Bill: pastes Johns, Bills, Dillans, then pastes jerry's Profit Sharing data in cell P2
.
.
Jerry: pastes Johns, Bills, Dills, then pastes Jerry's Profit Sharing data in cell P4.

The thing is, I don't want to change the coding I currently have too much, because I'm setting this up for different censuses (other workbooks with identical formats, but with more account balance sources which means additional rows under an individuals name on the account balance data sheet) with different reports, and would just like to automate doing this for 100's of people across different sheets with the same sheet names. So things need to be dynamic. Again I appreciate any kind of help on this.

I plan extrapolating so it wouldn't be just Profit Sharing, but also 401k, Roth 401k, etc.

Here's my current code:

Please Login or Register  to view this content.

Btw, the code line that says : Sheets("Table 1").Range("A" & i).Select

Is there anyway I can use this without having the sheet "Table 1" being the active sheet?

HESTER, JEREMY JEREMY HESTER M K N $1,958.30 $0.00 0.0000
HESTER, KEVIN KEVIN HESTER M K H $86,526.31 $136,455.35 0.0000
ALIZADEH, RACHAEL RACHAEL ALIZADEH 0 F N $17,337.50 $0.00 0.0000
BAYHAM, MICHELLE MICHELLEBAYHAM F N N $50,962.00 $0.00 0.0000
BECNEL, TESSA TESSA BECNEL F N N $0.00 0


Above is the table where I'd like to have the account balance data moved, to the far right column.

Account Description "Prior
"Account Balance" Transfers Withd(-) Earnings Losses(-) Forfeiture Current Contrib "*CurrentAccount Balance*" Pct Vested "VestedAccount Balance"
HESTER, KEVIN
PROFIT SHARING 1,045,567.51 0.00 75,445.09 0.00 52,000.00 1,173,012.60 1,173,012.60
Safe Harbor 200.00 200.00
401k Deferral
BAYHAM, MICHELLE
PROFIT SHARING 37,873.28 0.00 2,732.82 0.00 2,548.10 43,154.20 43,154.20
BECNEL, TESSA Term: 12/10/2013
PROFIT SHARING 1,369.20 0.00 98.80 0.00 0.00 1,468.00 20.00 293.60


Hope this helps.

Above here is where I'm extracting the data. So far I have the code to match up by name, then to move numbers under the column "Current account balance" to the furthest right column in the prior table. After identifying if there's a match between the two, it would extract the account balance should it be "profit sharing" and paste it.

The key here, is that I have other spreadsheets similar to the 2nd table that I've pasted, they're similar by format, however the account balance names differ. It may say "PS" as opposed to "Profit Sharing". i have gone through and created a bank of every combination of spelling variations for all the account sources. So I plan on expanding the code to accommodate those spelling variations if possible. I hope this helps