Copying Data to Other Sheets Based on Input to Single Sheet

[tre982]

Hello all! New to this board, and definitely a novice (but not complete newbie) with excel and seeing if anyone can help out with a task I'm working on.

I'm currently trying to create a budget sheet that breaks down into various sub-categories. I've created a summary sheet that pulls and consolidates data from my other sheets, but there are 47 sub-categories (meaning I've created 47 other sheets). Is there a way to use just one sheet to input all my data, use a drop down menu to choose which category it would fit under, and then have it auto-populate to the appropriate sheet?

For example: Let's say I have 2 categories: Work and Play. I spend $20 on a movie, $30 on dinner with friends, $10 on pens for work, and $10 on a networking lunch for work. With the way I have it set up now, I enter both the movie and dinner w/ friends onto my "Play" worksheet and the pens and lunch onto my "Work" worksheet. I've set it up so this copies over to my main summary page which shows that I've spent $50 on Play so far and $20 on Work for a total of $70 spent from my budget. But I had to enter it into each individual sheet. Can I just create one data sheet and use the validation dropdown (I already looked up how to set this up) and that would then automatically go into my previous sheets?

Any help on this would be greatly appreciated. The responses I've gotten from friends so far is "Just go buy Quicken."

 

[Trevor G]

Welcome to the Forum,

I assume what you want is to actually work on a main sheet and then once you add more data it distributes it based on the criteria.

For now look at this code and see if you can use it.

If you want to automate the process as you type it in you will need to look at the Worksheet code.

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Sheets("Action Plan").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub

 

[tre982]

Thanks Trevor! Will give it a shot.