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

 

[andrewb90]

It unhides everything when i add it to the current code, however, do I have to rehide the indivdual pages by typing each page to hide in the sub or is there a way to do it all at once?

I tried editing the code and it then rehides all sheets but one and then gives me an error.

Sub Splitsec()

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

Dim ws As Worksheet

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

Next ws

Application.ScreenUpdating = False

StartTime = Timer

i = 2

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

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

Sheets("Secure").Select
Worksheets(WorksheetFunction.VLookup(k, Worksheets("Secure").Range("B:E"), 4, False)).Select
Range("b65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

i = i + 1

Loop

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

EndTime = Timer

MsgBox Format(EndTime - StartTime, "0.0")

Application.ScreenUpdating = True

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

Next ws

End Sub

Any thoughts?

- Andrew

 

[vivekaholics]

You need to leave at least one sheet visible. So use this code

Sub Splitsec()

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

Application.ScreenUpdating = False

StartTime = Timer

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

Next ws

i = 2

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

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

Sheets("Secure").Select
Worksheets(WorksheetFunction.VLookup(k, Worksheets("Secure").Range("B:E"), 4, False)).Select
Range("b65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

i = i + 1

Loop

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

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = ws.Name = "Secure"

Next ws

EndTime = Timer

MsgBox Format(EndTime - StartTime, "0.0")

Application.ScreenUpdating = True


End Sub