Sorting inputted data into specific sheets

[davek804]

Hey guys, sorry for the double post. I posted this in the wrong forum, gonna go see if I can delete the other one now.




I am working on a project for a pseudo-accounting book. The goal is simple, to have the user input a single entry of expenses. Basically, that expense can be sourced from a bunch of different accounts - with each having its own tab.

So there are tabs for each account + one tab for the user to enter their data on. I want to have a drop down list (I've done this through data validation lists) that the user selects the name of the tab that the expense entry should be sorted onto.

Then the user describes the expense in each column with attributes like date, description, etc etc. When the user finishes entering this information, I want to have a SUBMIT button that then takes that row of data (which is a single entry expense-wise) and move it over to the proper tab (which should be able to be found out by looking at what is selected in the drop down menu).

Can anyone help me set up this submit button? I've spent a couple of days trying to figure out the visual basic code with no luck.

Thanks a lot!

[jaslake]

Hi davek804

This code may get you started

Please Login or Register  to view this content.

If you need assistance developing it further, let me know.

John

[davek804]

I've set that as the macro to my submit button and it fails to run, popping up a good ol error.

Could you explain the syntax to the macro? Maybe that would help me be able to figure it out. I was a comp sci for one year in my undergrad.. so I know a little bit about coding - but other than that I have no formal experience with VB.

Thanks a ton for the help!

[jaslake]

Hi davek804

The code works in the attached workbook.

This code

Please Login or Register  to view this content.

is saying the same thing as this line of code

Please Login or Register  to view this content.

This code

Please Login or Register  to view this content.

is saying the same thing as this line of code

Please Login or Register  to view this content.

Please bear in mind, the procedure only deals with entries on line 2 of each sheet. You'll need to develop iteration procedures to go through all the entries. You also need to decide what you want to do with data on the Entry sheet after it's been posted. If you need help developing this further, add more data to Entry worksheet (at least two transactions per target worksheet) then repost the file.

John

[davek804]

Wow, that works swimmingly. Thanks a ton, jaslake.

I've noticed as you said that it only deals with entries on line 2. The entry sheet is going to only serve as an input area, without data ever being stored.

However, I'm trying to have the data, when sorted onto different sheets, not replace the older info. So what I'm looking to do is basically have a list of entries on all the other sheets, with the newest ones just populating to the top, rather than replace the previous entry.

Would that be doable?

[jaslake]

Hi davek804

Are you suggesting the user will "Submit To Proper Tab" after each entry on the "Entry" worksheet? Or will they make multiple entries and then "Submit..."? Should "Entry" worksheet be cleared after "Submit..."?

Regarding this

So what I'm looking to do is basically have a list of entries on all the other sheets, with the newest ones just populating to the top, rather than replace the previous entry.

Yes, this is possible. Although, it would be cleaner to append the new entry on the next available row. Is this acceptable or do you REALLY want the newest entry on top?

John

[jaslake]

Hi davek804

This code assumes the user will "Submit To Proper Tab" after each entry. It also assumes you wish to have "Entry" sheet cleared after "Submit...". The entry is placed at the top of the list in the Target worksheet.

Please Login or Register  to view this content.

If the user will make multiple entries before "Submit...", the code will need to be modified to accommodate that.
Let me know of issues.
John

[kcj784]

Hi Jaslake, i had a question about a somewhat similar sheet, could you possibly take a look?
http://www.excelforum.com/excel-prog...worksheet.html

[davek804]

jaslake,

You are helping out amazingly, thank you so much.

Yes, unfortunately the person this workbook is destined for wants to have the oldest entries at the bottom of a given sheet, with the newest ones populated up top. So you've coded it exactly as they want. Clearing out the data from the entry tab is also a desired characteristic.

I don't think I'll need to have multiple entry rows, such as five rows or anything like that. The only other functionality that is needed at this point is the ability to sort a given entry into multiple accounts - the tabs I have right now are just test tabs, I will have to add in a few new ones and change the names etc. That being said, I've uploaded the excel sheet with your changes plus the desired number of available accounts. Could you please show me how to change the macro to paste the data into a few accounts at once when desired?

You'll see I have columns for Acct 1, 2, 3 and 4. Each offers a dropdown list of accounts to choose from, I'll probably end up with six or so accounts. Essentially I want the user to be able to make an entry with only one account, with two, or whatever combination desired. Obviously the user will be intelligent and leave accounts 3 and 4 blank if only two accounts are needed (use 1-2 instead).

You've been such a great help, it's folks like you that make message boards such a valuable resource!

[jaslake]

Hi davek804
I see in your new Entry worksheet you've four account columns and one amount column. Will the same amount be posted to all accounts? If so, I'd suggest you do this vertically, not horizontally. The code you have thus far won't work with the horizontal layout.
If you're determined to have the horizontal layout, you need to develop the project further so I can see what it's going to look like before code can be applied to it.
John

[davek804]

Hey jaslake,

I sent you a PM explaining what is hopefully the final change to this project before it's completed. I mentioned that I'd attach the excel file to the thread, so here it is.


Again, thanks so much.
Dave

[jaslake]

Hi Dave

In your Entry Form, you have a $90,000 expenditure and two accounts chosen ("Gift" and "CPE"). Does each account get posted with $90,000?

You mention in your PM that

The vast majority of the time, it will only be Account 1 selected, while Account 2 is left blank.

The way your data validation is set up, two accounts will ALWAYS be selected. This will need to be changed.

Get back to me and then I'll look at it.

John

[davek804]

John,

Every number in there is just artificial for planning purposes. But for that example, the $90,000 would EITHER go to two accounts OR just Account 1 (which the user would select from the drop down list as whichever account desired)

Essentially the macro has to sort data based on what is entered in G2 and H2 (the two account selection lists). So if the user only has G2 selected, the data will only be moved to the ONE corresponding account tab. If the user has two different accounts selected (G2 and H2), then the macro must sort the data to both accounts that were selected.

The values would ALWAYS need to be duplicated rather than divided, there will never be a situation where $44,000 goes to Account 1 and $46,000 goes to Account 2 all from the same singe entry.


Thanks.

[davek804]

Also, I've updated the data validation in H2 so that there is an available blank space below the other options, this being what you mentioned in your last post.

Does that create a suitable environment for the macro to run in?

Thanks

[jaslake]

Hi Dave

Although I put the "Blank" at the top of the list, this will work

I've updated the data validation in H2 so that there is an available blank space below the other options

Paste this code in the "Entry" worksheet (left click the worksheet tab, select "view code"):

Please Login or Register  to view this content.

Here's the code to post to one or more accounts:

Please Login or Register  to view this content.

See if this does what you described. Let me know of issues.

John

[davek804]

Very close!

It went through and pasted the data to the proper two tabs the first time, then I went ahead and tested with Account 2 blank and ended up with an error, 9 again. The dubugger highlights

Please Login or Register  to view this content.

which is the 3rd line.

Basically the functionality is exactly what I want. LIke I said, it did perfectly the first time through but then catches up on something, but I can't tell what. I've tried to clear out the entry, add in new data, and tried all sorts of combos between the two sets of accounts. Do you think there's an exception somehow missing or something like that?

Thanks,
Dave

[jaslake]

Hi Dave

Make certain your TAB names are spelled the same as your Data Validation Names. I see at least two that are different. Subscript out of range means vba can't find it. Vba can't find it because it doesn't exits as spelled.

See if that fixes your problem.

John

[davek804]

Wow John, way to point out the obvious that I missed. I had to switch the names of the tabs to no longer contain spaces for a similar reason, but I forgot to rename the sorted listed, how awesome.

Well, anyways, this project now looks like it has all the functionality my higher ups desire, so I will present it and see if they like it. Thank you so much for the help John, I certainly would not have figured out this macro without your incredible help. I only wish I could spam the reputation button a bit more.

Dave

[jaslake]

Hi Dave

Glad it works for you. I'd probably add an Else statement and a Message Box if G2 Value is "". I'd suggest to the User they needed to add a value to G2. If you need help with that, let me know. Else, please mark your thread as "Solved" if it suits your needs.

John