Automatically paste $ data from 1 master worksheet to 100's of separate worksheets by name

I don't have a clue how to do this or if it can even be done.

After a check run of hundreds of checks (pay to the order of John Doe), I create a "master" list with columns for name, date, check number, description, and so on. There will be a total of 10 columns and hundreds of rows. We currently have to enter these data into hundreds of ledgers (worksheets) in a single excel 2003 workbook with a separate tab (worksheet) for each client and we do it by typing on the keyboard. Creating the "master" list takes only a few seconds. The entries take hours and hours weekly.

The goal is to take the data in that newly created "master" worksheet (hundreds of rows) and copy and paste those data automatically into the other existing worksheets (ledgers) that are matched with the name in the first cell of each row of the "master", but without pasting the name into the ledger.

Cell A*= Doe, John

The code has to find the worksheet tab "Doe, John" and then copy and paste the range of 9 cells that follow the name, but not the name. It must paste into the first empty row below the existing data without overwriting previous data. Some of the existing worksheets (ledgers) are new and have only a few rows. Others are years old and have hundreds of rows.

Some "master" cells will have data, some cells will be empty, depending on if the $ is a credit (deposit) or the type of expense. Different things go in different columns. Within each row, there will always be some empty cells. The first 4 cells will always have data. The cells will not all have the same formatting within a row. There will be date formatting, general formatting, text formating, and currency formatting. Within a column, formatting is always the same.

Example 1 master:
Doe, John-----4/6/13-----25695-----Personal Expenses-----(empty)-----(empty)-----(empty)-----(empty)-----(empty)-----$97.42

Result 1 ledger/worksheet:
4/6/13-----25695-----Personal Expenses-----(empty)-----(empty)-----(empty)-----(empty)-----(empty)-----$97.42

Example 2 master:
Doe, Jane-----4/6/13-----deposit-----commission-----$225.00-----(empty)-----(empty)-----(empty)-----(empty)-----(empty)

Result 2 ledger/worksheet:
4/6/13-----deposit-----commission-----$225.00-----(empty)-----(empty)-----(empty)-----(empty)-----(empty)


The cells in the middle are for certain fees, savings deposits, saving withdrawals, rent, etc. Sometimes they contain data and sometimes they don't. They never all contain data on the same row.

The new "master worksheet" will be in the first position every time and will be updated as needed.

I should have included a sample ledger with the original post to save everyone's time; so, I have added it here.

Something needs to indicate that a "master worksheet" row did not copy and paste to a ledger. A name might be misspelled or a worksheet may not have been created yet. It happens. If errors weren't shown, the accuracy of the ledgers would degrade over time. The actual ledger file has hundreds of worksheets and is 15 megs and growing.

Sample file uploaded. The first worksheet is how the "master" file will look. The other tabs are the actual ledgers with nonsense entered for appearance. The last columns are static formulas for running balances. It would be nice if I could add the formulas to the "master" so they copied too and I wouldn't have to drage them down later. That would be twelve cells after the name instead of nine.

Afterthought to the original post: Something needs to indicate that a "master" row did not copy and paste to a ledger. If successfully copied rows were deleated (copy, paste, cut), and failed rows remained in the master list, I can go back and see what went wrong, like a misspelled name. It happens. If errors weren't shown, the accuracy of the ledgers would degrade over time. The actual ledger file has hundreds of worksheets and is 15 megs and growing.

Thanks jolivanes. I imported the macro into my sample workbook and ran it. It did paste data from the master across the ledgers; but, it repeated the same datum on each ledger and overwrote all the original entries. It also knocked out one of the header rows. I should have uploaded the sample file with the original post. This is pretty hard without it. Sorry, won't happen again.

It needs to find the first blank row at the bottom and begin pasting from there.

Thanks AB33. I imported the macro into my original sample file. It worked well enough to convince me that this can probably be done. The maco pasted the correct rows to the correct worksheets. Unfortunately, it wiped out and overwrote all the original data in every worksheet and changed all the formating. It has to find the first blank row at the bottom of the existing data and paste from there down leaving the existing formatting intact.

IT WORKED! Accolades and praise to AB33. It took 19 seconds to copy and paste 257 entries. That used to take 6 to 7 hours.

I tried AB33's code on the sample ledger; and, it worked flawlessly. The only concern was that I didn't see a means to identify errors, i.e. some notification that a row did not properly post to a ledger. As a test, I added some bogus rows at the bottom and ran the macro again. To my delight, it dealt with the error rows by adding more worksheets at the end with the new tabs labeled with the erroneous names. That's great.

I'm going to upload sample ledger3, which will have several bogus rows mixed in for anyone else who wants to try it. I also added in the three columns that maintain running balances to the right and column totals at the bottom. AB33's code nests the new data right in between all the existing data starting with the first blank cell after the last filled cell anywhere in column A. I will also leave the macro attached. I press alt-f8 to run the macro.