I have an accounting worksheet that includes columns for date, description, debits, credits, balance, account, and type. The type column is a pull down menu including all expense and income categories (ie. home, car, food, etc.). I have separate worksheets where I currently copy-paste appropriate rows based on type.
I want the spreadsheet to copy automatically as I add entries to the base worksheet throughout the year.
How can I do this?
Will it be possible for you to attach a sample file with dummy data?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
This is my first time posting here so bear with me. Hopefully this works.
The base worksheet is "Data Input...". Under Category I select the appropriate expense/income. Then I have separate tabs for each type. I want the document to sort through each entry in Data Input based on its category and place them in the proper worksheet.
I tried the Advanced Filter under the Data pull-down menu, but the problem was that it seems to only work if entries into the spreadsheet are complete (ie. at year-end). If I want a running total throughout the year, it appears as though I'd have to run the filter for every worksheet every time I want an update during the year.
There must be a better way. I hope you can help.
Do you want the data to transfer on the click of a button or as soon as its entered in the main sheet?
Either way is fine as long as I don't have to do a lot of work every time I want updated information.
Use this code -Just ensure that whatever entries you have in the "Category" field matches the sheet names.
Please Login or Register to view this content.
Where do I apply the code? Is it going in exactly as you've shown or do I need to modify anything to suit my situation?
Press Alt + F11. You will see a blank code window. On the left hand side you will see microsoft excel objects. Right click n select Insert->module. Copy the code in. Then go back to your excel sheet. Go to Tools -> macro -> macros and run it.
You dont have to change anything, just ensure that whatever entries you have in the "Category" field matches the sheet names.
I don't see "microsoft excel objects" anywhere. Therefore no insert -> module either. Does it matter where i click on my excel sheet?
Nevermind, I got it to run but it only copied the first entry (mortgage). Nothing else.
When i ran it, it worked fine at my end. Ensure that the sheet names are the same as the category names.
I'm getting tis error message:
Microsoft Visual Basic
Run-time error '9':
Subscript out of range
With options "End" "Debug" "Help"
It hit "Debug" and it highlighted this row: lastrow = Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Row
It appears that only 10 entries worked (right now I have 42 entries that should have been copied).
When I run it again and hit "End", nothing more happens.
What do I do?
Can you attach the sheet you are using with the macro? I can try testing at my end. Have you checked that each tab name matches the categories listed?
Try that one.
I ran your code at my end. The only time i got the error was when the tab was not consistent with the category name listed in column F. The category was E-Home-Computer. The tab showed E-Computer. Otherwise it works fine. So ensure that your categories and your tabs match, then you will not face any such errors again.
Well I've been busy for a while but I'm back at it again. Thanks for picking up on that incorrect tab name. I've got that fixed and the no more bugs. It seems to run, however it only puts one entry into any given tab/account from the data input sheet. It is not listing all entries under, say "E-Home-Mortgage", or "I-Ian", but just one of them. Is that the way you intended it? I want it to list everything that matches the criteria so it can then add them up.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks