quickbooks data in excel copied to excel form for printing 1099-S

[houndhen]

I am a long way from an excel guru. I help a customer with their year end tax forms. This year they had to do 43 1099-S forms. They use Quickbooks Pro. QB will process the 1099-misc forms but not the 1099-S forms. What I do is export the QB data needed to Excel and then get it set up in a database. I also have another Excel form that when the QB data is entered into it will print on the pre-printed IRS tax forms. In the past I would copy and paste or just type the items into the form that will print on to the pre-printed IRS tax forms. Quite tedious and time consuming.

What I would like is a macro to go through the database and copy the data to the Excel printing form. Each 1099-S form is three to the sheet. The data from the database will have to be put into certain cells on the Excel printing form. The items in the database will be Name, Address, City, State, Zip, SS#, and amount paid. The name will go in one cell, the address will go into another cell, the city, state, zip will go into another cell, the SS# into another cell and the amount paid into the last cell. These cell that will receive the data are not in the same row or column. The name, address, city,state,zip will be in the same column in three consecutive rows but the other two are elsewhere on the form.

If I had something to start with, then I could probably edit it to make it work for me. Does anyone have an idea of some code that I could start with?

Thanks in advance,
Harold

[skywriter]

You should post an example spreadsheet with some dummy data and then you'll probably get help. Without knowing what's where and what the sheet names are and many more variables I don't think someone is going to just be able to explain to you how to do it. I'd certainly be more than happy to take a crack at it for you, I do this as a hobby, so it's fun for me.

Good Luck!

[houndhen]

Thanks, I figured I would need to give more information. Actually I went ahead and started the macro recorder and got something that I think will work. I am on the way to work but will post back when I have time. My macros are not pretty or efficient code most of the time but it seemed to work for me. More details to follow.

[houndhen]

OK. Just getting back to this because it might help someone like me that is less than a guru. I can't say enough good about the macro recorder.

I have a workbook with several worksheets in it but the two main ones are '1099sData', and 1099sform. The 1099sData is the data that I want to put into the 1099Sform. I had spit out a report from Quickbooks with this data and then had to manipulate it some to get it in the right format. The 1099SData form has these columns: Name, Address, CityStateZip, SS#, Amount. The 1099SForm worksheet has the company information with the address and fein - everything needed to go onto the IRS form as well as holding places for the data that will come from the 1099SData worksheet. The 1099S form will hold the data for 3 people and it has to fit on one sheet. Also I have found out that once you get your form 1099SForm aligned with the IRS form, if you change printers then you have to re-align.

I wanted to be able to transfer the 1099SData for 15 people at a time so I copied and 1099SForm until I had 5 exact forms. I then had to rename them 1099S1 thru 1099S5.

Anyway, I started with the 1099SForm(#1) and selected the cell where the name was to go and started the macro recorder. Then I activated the 1099SData(#2) and selected the cell that held the first person's name, hit CTRL C and switched back to #1, CTRL V and moved the cursor down two rows where the address was to go. I then selected #2 and selected the cell that held the person's address, hit CTRL C and switched back to #1 and hit CTRL V, moved the cursor to the next place to receive data. I kept doing this until I had filled out the 3 1099 recipients data on the first sheet. I stopped the recorder. I then edited the macro to make sure everything worked. I had to put in a 'for next' loop to loop thru the 5 pages. I also had to put in a counter to make sure the row incremented each time to get the data for the next person. If I have more than 15 1099S' to do then I will have to add more form worksheets and adjust the loop number or just start it over but change iRow counter to correspond to the 16th person to receive a 1099-S.
If this is not clear enough or anyone would want a copy of the macro then send me an email and I will be glad to send you a copy.