Populate data from one main sheet to multiple sheets

**dfulmer** · 12-28-2012, 08:14 PM

Hi,

I have a spreadsheet with 10 columns, each column with various criteria such as Date, Day, Time, Content Pillar, Platform, Status, Approval Date etc. The data in the Content Pillar column is based on a drop down with four options, all other columns have static (manually entered) information. I am trying to populate this data onto different sheets representing each Content Pillar. For example, if one of the Content Pillar cells has "Entertainment" then I want that whole line of data to populate on the first row of the Entertainment sheet. If the next line has Education, then I want all of that data to populate onto the first row of the Education sheet. Then if the third line of data has Entertainment again, I want this whole row of data needs to get populated to row 2 of the Entertainment sheet. I do not want a blank row 2 and the data populating on row 3.

I have tried using VLOOKUP, but it only will select the first instance of each Content Pillar and repeat that in each row.

Hope this makes sense - feel free to ask any questions. I would provide the spreadsheet, but it contains a lot of confidential information.

Thanks!

**jeffreybrown** · 12-28-2012, 08:18 PM

Hello & Welcome to the Forum,

You are going to need a macro for this and one of our forum members has written a procedure make specifically for this task...

http://www.excelforum.com/tips-and-t...-criteria.html

**dfulmer** · 12-28-2012, 08:35 PM

Hi Jeff,

Thanks for the quick response. I just tested out the macro, however I need something that is not just a one timer. I ran it once, it worked, and then I changed one of the Content Pillars to something else and ran it again but it didn't update this change. This main list will constantly be added to and possibly edited.

Any thoughts?

**jeffreybrown** · 12-28-2012, 08:43 PM

Take a look at this thread, post #6 and see if you can tweak it to your needs.

http://www.excelforum.com/excel-prog...s-are-met.html

If not, please let me know...

**dfulmer** · 12-28-2012, 09:08 PM

This looks like it will work, however how do I get it to start? I am not familiar with macros at all. I tried doing what I did with the previous macro you shared. Open VBA editor then Insert-->Module and then paste it in. Am I missing something?

**jeffreybrown** · 12-28-2012, 09:15 PM

Looks like you were headed in the right direction, but you need to place the macro in the worksheet module.

The macro is acting off of column E. When one of the four key words is selected, that key word is matched up with the matching tab and the data (entire row) is copied and pasted. In your case, not sure what column you have your key words, so update as required.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sname As String

If Range("J" & Target.Row).Value = "Yes" Then
    sname = Range("E" & Target.Row).Value
    Target.EntireRow.Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Target.EntireRow.Delete
End If
End Sub

Does this help?

**dfulmer** · 12-28-2012, 09:58 PM

Excellent! It now copies over. I edited the code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sname As String

If Range("D" & Target.Row).Value <> "" Then
    sname = Range("D" & Target.Row).Value
    Target.EntireRow.Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub

I am having it check if that cell is empty before it begins running. I don't want it to delete the line from the main sheet, so I removed the Delete line.

Now this is where I think I am SOL. I originally was going for a formula so that the spreadsheet would update live whenever there were changes made. If the criteria gets changed (such as Entertainment to Education), I need it to remove the line from the old criteria's sheet and add it to the new one. Currently, it adds it to the new criteria but doesn't remove the old listing. Also, now that it doesnt delete the line off of the main sheet, if I select the line again it will copy a duplicate entry onto the sheet.

Any thoughts on how to get around this?

By the way, I REALLY appreciate your help!

**jeffreybrown** · 12-28-2012, 10:13 PM

I see what you mean, your requirements could be complicated, but there may be some solutions.

So, if the requirement changes, are you going to create a new sheet?
Since you are not deleting anything off of the master list, does this mean you would delete the old listing off of only the individual sheet?
Why would you not delete the entire sheet since it is the old listing?
How often would a new criteria change?
Are you actually saying the criteria would change on a previous existing row and not just added as as addition?
An often asked question, why even deal with the individual sheets, why not just use filters to filter the master sheet to any specific criteria?

Sorry for all of the questions, but the requirement are not entirely clear to me.

Give these questions some thought and maybe a workbook example with one specific criteria change and what you want to see take place.

**dfulmer** · 12-28-2012, 10:44 PM

The four criteria will never change, therefore I will never have to delete or create a sheet
I meant that anything on the master list should be on a sub-sheet, and visa versa. Technically, I never want to have to edit anything on the sub-sheets, just anything that happens to the master sheet reflects on the sub-sheets
answered in 1
Criteria would rarely change, but it is still a possibility. This is not a huge priority of mine for this project though, because if all else fails I could always just delete the listing and recreate one with the correct criteria (although that is a bit annoying)
It is possible that a previous existing row's criteria could change
The individual sheets make it easier to share the specific information to the appropriate team member. Filters COULD work, but that is not what we are trying to do here. I thought this would be an easier task when I took it on. Also, my boss asked me to do it this way and he knows filters are an option.

Ideally, I want a master sheet that I can enter the information in and it can also be displayed on its appropriate sheet. I should never have to edit a sub-sheet. This is why I originally tried to work with formulas so that it would be live updating. I basically had it, but I couldnt figure out how to avoid blank rows in the sub-sheets. For example, on the main sheet if row 1 is Education, row 2 is Entertainment, and row 3 is education again, I don't want row 2 on the education sheet to be a blank row. Is there a formula that can delete a row if the value comes back blank?

I also tried to get something along the lines of this thread: http://www.excelforum.com/excel-new-...le-sheets.html but its a pretty complicated example and I was having trouble getting it to work with my one criteria. Maybe you would know how?

I can send you a workbook later, I would need to go through and remove any confidential information but still preserve what I am trying to do here - it will take some time.

**jeffreybrown** · 12-28-2012, 10:57 PM

Well given everything from above, it sounds like this worksheet change event will work perfectly fine.

As soon as you enter the information in the master sheet it will show up on the individual sheet.

The one thing to keep in mind though, the row needs to be completely filled out before you enter info in the key column.

As far as the attached thread, Jerry has done some fancy formula work which I'm not quite up too on his level.

Its been quite the long day for me and my brain is working on one cell right now so it seems I'm not thinking right at the moment.

I'll hang in there with you and when you get a sample maybe something will spring to light.

**dfulmer** · 12-31-2012, 05:24 PM

Hi jeffreybrown,

I have an attached an example spreadsheet. I also sent a PM to Jerry to see if he had any insight on how to make a formula work with this, and he requested that I create a new post in the General section and attach a spreadsheet and he would respond. Here's the link to that: http://www.excelforum.com/excel-gene...32#post3063832

Let me know if you can figure out anything for live updating. Once again, I really appreciate all of your help.

Thanks!