Transfer data to multiple spreadsheets - Accounting

Hi there,

I am looking to Enter data (from Data Entry sheet) by selecting Category and Month, and have the data in the next two cells (D4 & E4) transfer to the appropriate sheet, after hitting "Submit" or enter.
I thought this was going to be easy, but once I had to add the second search criteria I think I bit off more than I can chew. I have basic Excel skills.

Thank-you so much for your time.
KTESTCopy of 1Fund-BudgetFinancial_Template_v3.1.xlsxTESTCopy of 1Fund-BudgetFinancial_Template_v3.1.xlsx

What is the "appropriate sheet" for the data to go to? Where does it go on that sheet?

If you could provide some examples of "if I selected... then D and E would go to this sheet on this row..." that would be helpful because it's not clear to me.

For example if "wages", and "July" were selected contents from D4 should go to GL-JULY!B211
E4 should go to GL-JULY!D211

I know it's a big project. I just need help getting started.

Just wondering if you were able to look at this...

Thanks!

I can write you the code specifically for the example you gave, if that will help you. But I still don't understand the general rule as to WHY wages and July would correlate to that row (I understand why that sheet), so writing more general code that you could extrapolate out to all the choices is going to be difficult.

Basically, I would like the user to select a "category", and an "month" then enter in some information. I would like that information to transfer to the correct sheet by month and then down to the correct category.

I made a form. If the user selects "Amortization", and "January", then click "submit" the Date would transfer to sheet "GL-Jan", row GL-Jan!A103, details row GL-Jan!b103, and amount GL-Jan!D103. If the user selects "Amortization" and "Feb" the info would go to sheet "GL-Feb", Date GL-Feb!A103, Details GL-Feb!B103, and amount GL-FebD103. If the user selected "Insurance" and "January" then Date, row GL-Jan!a112, GL-Jan!b112,GL-Jan!d112.

Does that help at all?

In the attached spreadsheet, I gave up have the two search critieria (Month and Category) and went with just Category. The user would go to the "Data Entry" sheet, pick a month, and enter the information, hit "sumbit" and the data would flow to the correct spreadsheet. I am not sure which way is easiest.

Thanks for your time.

Did that help at all, or still confusing?

I get what you're saying. Your formatting choices make the coding somewhat...convoluted I guess. I'll do the coding for one option, hopefully you can extrapolate.

As a suggestion, I would instead have all the user input exported to a single sheet as a new line item every time, with the data type (insurance, interest, etc.) as a unique column. You can see in the code how not knowing where to put the data without searching makes the code unwieldy and lengthy.

What I mean by that is:
we don't know which row to add the data to because you may already have 1+ other entries of that type, and
if you need more than 4 entries for each category, you're going to have to insert more rows, shifting everything down.

Like i said, we're forced to search for a blank row in the correct category every time.

Well anyway, here's the code. It's not easy to read, and it's going to be tedious to include all the options you have, but it will technically work. This is for just one button, where the user selects month and data type, not the form method you later posted. And it is for one single option: if the user selects both January and Insurance.

Please Login or Register  to view this content.

That was just what I needed! Thank-you so much! It looks awesome! I tweeked it a little, and was able to put it into a userform afterall. I had to add another button for July-Dec , because of all of the lines of code. Once I had one month completed, I cut and copied it into word, and used the "replace" feature to speed up the process. Here is the code I used:

Please Login or Register  to view this content.

and to change month

Please Login or Register  to view this content.

Well glad it works for you.

Also, you might want to think about adding code for inserting a new row if cRow > 3.