Sorting inputted data into specific sheets

**davek804** · 06-18-2010, 11:41 AM

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** · 06-18-2010, 02:44 PM

Hi davek804

This code may get you started

Public Sub Post()
Range("A2").Resize(1, 7).Copy Destination:=Sheets(Sheets("Entry").Range("A2").Value).Range("A1")
End Sub

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

John

**davek804** · 06-19-2010, 02:25 PM

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** · 06-19-2010, 04:22 PM

Hi davek804

The code works in the attached workbook.

This code

 
Range("A2:G2").Copy

is saying the same thing as this line of code

 
Range("A2").Resize(1, 7).Copy
'Resize Range A2 to 1 row and 7 columns (A2:G2)

This code

 
Sheets("ProCard").Range("A1").PasteSpecial

is saying the same thing as this line of code

 
Destination:=Sheets(Sheets("Entry").Range("A2").Value).Range("A1")
'(Sheets("Entry").Range("A2").Value is "ProCard"  so the line evaluates to
'Destination:=Sheets("ProCard").Range("A1")

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** · 06-22-2010, 10:26 AM

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** · 06-22-2010, 11:16 AM

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** · 06-22-2010, 11:40 AM

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.

Option Explicit
Public Sub Post()
    Sheets("Entry").Range("A2").Resize(1, 7).Copy Destination:=Sheets(Sheets("Entry").Range("A2").Value).Range("A1")
    Sheets(Sheets("Entry").Range("A2").Value).Range("A1").Resize(1, 7).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Entry").Range("A2").Resize(1, 7).ClearContents
End Sub

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** · 06-22-2010, 11:59 AM

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** · 06-22-2010, 12:28 PM

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** · 06-22-2010, 04:12 PM

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

Sorting inputted data into specific sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Re: Sorting inputted data into specific sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions