Copy row data from one sheet to another depending on a category

[Burli]

Hi, I am trying to create a spreadsheet to track expenses for a club that I belong to. The front sheet is the "master" sheet where all expense transactions would be entered by date, one for each row, the number of rows growing as the year progresses, and each row has a drop down box for the category of expense, eg subs, admin, insurance, speaker fees etc (about 10 categories). I would then like to copy the data from each row to another sheet, like a sheet for subs, another for admin etc, placing the data in the next available row, so the sheet for each category will grow with time and I can add formulae to track the totals etc.
I was thinking that I could use the "worksheet change event" to trigger the copy but have got into a big mess. Any ideas please?

[arlu1201]

Do you have a sample that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Burli]

Hello, I'm sorry but I don't really have much to upload, just the layout of the sheets, the rest was being planned on paper and I have got myself completely mixed up trying to work out how I can make it copy a row and then paste it into another sheet on the next available row based on the category used in a drop down box on the first sheet. I was thinking of starting all over again.

[arlu1201]

Maybe you can provide me some details.

In which column will the dropdown containing the category be?

Which is your column containing data?

[Burli]

Here is my basic sheet, it can change as necessary but I was just trying to see if I can get something to work in principle. The drop down category box is in column G and the drop down range is in column Z. The data will be entered in the Transaction sheet, and then hopefully will be copied to the other sheets.

[arlu1201]

Currently, you have many categories but sheets for only 2 categories. Do you want the macro to create the sheets for the categories which do not have sheets?

Also, do you want the macro to be executed when you select the value in the dropdown (instant) ? Or will you populate the category for all rows and then click a button for the macro to be executed?

[Burli]

Hello, thanks for your reply. This project was really in its early stages and I only added the two category sheets to be able to try different scenarios. Ultimately I intend to add a sheet for each category, and to have a subtotals on each sheet which will update the final report. That I can do ok, but I was stuck trying to get the data from the transaction report to each category sheet and get it to copy to the next available row. I think that the macro could be executed from one button each time after the rows have been updated, but if you think it is better to update after each row has been entered. The rows in the transaction report may be added several times each month. I hope that makes it a bit clearer.

[arlu1201]

I think that the macro could be executed from one button each time after the rows have been updated, but if you think it is better to update after each row has been entered. The rows in the transaction report may be added several times each month.

It depends on what works easier for you. If you want it to be copied each time you change the dropdown, it can be done instantly without any button. However, that can create potential issues of having duplicates, incase someone selects the value in the dropdown twice.

So i guess to make it easy for you to manage, its better to have a button which you can click on after all the updates are done.

So if you update your main sheet today and run the macro, and then add in more data tomorrow, will you add this data to the next available row of your main sheet? Or will you clear the main sheet and reload with fresh data? I am asking you this, so that i know what action to take for the other sheets. If you are going to load new data starting from the next available row of the main sheet, then i need to clear the other sheets and start loading from row 2 to avoid any duplications. But if you are clearing the main sheet and loading new data, then i dont need to clear the other sheets.

[Burli]

Hello, the data will always be added to the main sheet on the next available row so at the end of the year the main sheet will show all the transactions that have been entered for the year, and when the data is added to a new row on the main sheet it will be copied to the other sheets depending on its category so that the category sheets will also grow as the year progresses. At the end of the year we would see all transactions as entered on the transaction sheet, and then each category sheet will show all transactions that have been entered for that category. In my mind I see what I want but maybe it is hard to explain!

[arlu1201]

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Ensure that the extra column A in the 2nd sheet is deleted. The format for all the sub sheets should be the same. The macro will create any sheets if required so you do not have to create them in advance.

[Burli]

Hi, you are truly amazing, your code is doing exactly what I want it to do and you make it look so simple. I am ashamed to say how many lines of code I had written out trying to do this. I will try out more scenarios with your code but I think that it is perfect, and I will update the forum tomorrow to close it as solved.
Many thanks again.

[arlu1201]

Am glad it worked for you.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[Burli]

Hello, yes I did say that I would close it and mark as solved today after I had tested it. I just had to make a slight change to the code to stop it clearing a summary sheet that I have added, but I am very pleased to say that it works very well and you deserve many many thanks for helping me.