pulling info into multiple pages

[andrewb90]

I am working on a project where I have list of tasks in column B. (This can be a few dozen or several hundred depending on what is going on) In columns C,D,E I have a 'key phrase' or 'value' assigned to each task. For example the first task is scheduling employee requests. I would put that in a category for 'schedules' and for 'management'. The categories could be represented by a number if that would be easier for Excel to recognize.
Now in Column F and G I would like to select the individual(s) assigned to the task. I was thinking of typing it in or selecting it from a drop down.
The tricky part here is that I have a separate sheet for each person, And I would like that info from the row where I selected or typed in the name to appear on that persons page.
The idea here is that I take my task list, however long, and select an individuals name and have that task appear on the individual page for that person. so that I don't have to copy and paste each task on 1 of 40 different pages.
The 'value' of each task isn't nearly as important as figuring out how to move the info from the main sheet to all the individual sheets. I hope this makes sense. I'm fairly new to Excel, so any explanation as how to make this work would be greatly appreciated!

- Andrew

 

[vivekaholics]

I am assuming your list of tasks start from Cell B2 (Heading in Cell B1) and names start from Cells F2 and G2 (Headings in F1 and G1). Give this a try. This code will work for any number of tasks until the code bumps into blank cell.

Sub Split()

Dim i As Integer
Dim k As String
Dim StartTime As Date, EndTime As Date

Application.ScreenUpdating = False

StartTime = Timer

i = 2

Do Until Trim(Sheets("Sheet1").Cells(i, 2)) = ""

Sheets("Sheet1").Select
Cells(i, 2).Select
k = Cells(i, 2)
Selection.EntireRow.Copy
Worksheets(WorksheetFunction.VLookup(k, Worksheets("Sheet1").Range("B:F"), 5, False)).Select
Range("b65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Worksheets(WorksheetFunction.VLookup(k, Worksheets("Sheet1").Range("B:G"), 6, False)).Select
Range("b65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

i = i + 1

Loop

Worksheets("Sheet1").Select
Range("a1").Select
Application.CutCopyMode = False

EndTime = Timer

MsgBox Format(EndTime - StartTime, "0.0")

Application.ScreenUpdating = True

End Sub

 

[vivekaholics]

One more thing, I also assumed that you have your raw data in Sheet named Sheet1.

 

[andrewb90]

So, I am a bit confused with this code. I put the code in and, I am not sure how to proceed. I have at the moment 2 pages created one saying Sam and one saying Julie. Now in Column B (starting at B2) I have the task. Then in C,D,&E. I have 1 or more 'keywords' for that task. Now then I type Sam's name info F2 and nothing happens. I even tried assigning the code to a button and clicking but I get error 400, row 2 (the only full line) selected then sent to a cell on the Sam page. Am I doing something wrong? I am using Excel 2010.

Thanks,
Andrew

 

[andrewb90]

Also can this update the other pages automatically? Or do I need to do something special after entering data?

 

[vivekaholics]

You first need to populate the raw sheet (Sheet1) completely i.e, Each Task in col B should have a name assigned in Col F and Col G. Further, you mentioned that you already have worksheets with names, so we are not adding any worksheet with this code. There should already be worksheets for all the names in Col F and Col G. I have uploaded a dummy file for you at below path

http://www.mediafire.com/?133c1l7er6nbgo8

You need to run macro in this file and it will copy complete row for all the tasks and paste in respective worksheets of names assigned in Col F and Col G.

 

[andrewb90]

Awesome it seems to work great! I wanted to see if it was possible to have a function (either separately or combined) that would allow me to clear all the info from all the people's individual sheets. Is something like that doable?

 

[vivekaholics]

You can use this code but just make sure that your raw data or Sheet1 is the first tab of your workbook. This code will delete all the contents except the content on first tab/worksheet.

Sub clearall()
Dim y As Integer

z = ActiveWorkbook.Worksheets.Count

For y = 2 To z

Worksheets.Cells.ClearContents

Next y

End Sub

 

[andrewb90]

Thanks, I'll give that a try. However I just encountered a new problem today. Because I have so many sheets with names on them I have most of them hidden. The problem is I get an error if the sheet is hidden. Is there a way around this?

Thanks!

Andrew

 

[vivekaholics]

you can unhide all worksheets with this code

Sub UnhideAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = True

Next ws

End Sub