Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day of the
week) that can be used throughout the week e.g (Mon1, Mon2, Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2, Fri3, ....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11 will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an input
cell to accept a teacher name in order to display the weekly program of the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly program
of that class, but having the involved lesson_name+teacher name as matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

Sorry for the tipo

"Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher, a blank cell meaning an empty period for that teacher."

should have been

"Range B2:AY11 will hold data of "lesson_name+classes" info for each
teacher, a blank cell meaning an empty period for that teacher."

Regards
J_J



"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day of
the
week) that can be used throughout the week e.g (Mon1, Mon2, Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2, Fri3,
.....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11 will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an input
cell to accept a teacher name in order to display the weekly program of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly
program
of that class, but having the involved lesson_name+teacher name as matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose = True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose = True

Since you don't describe anything that relates teacher to social activity or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day of
the
week) that can be used throughout the week e.g (Mon1, Mon2, Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2, Fri3,
.....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11 will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an input
cell to accept a teacher name in order to display the weekly program of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly
program
of that class, but having the involved lesson_name+teacher name as matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

Hi Tom,
Thanks for your answer. Sorry for not giving sufficient info.
I was not after the procedures that copies certain rows/columns to Sheet5.
This can partly be done with just copy/paste and filling in cells. The
problem I have is related with Sheet 6 & 7.
On Sheet6 I should be able to input a name, and if that name is the same as
one of the teachers on Sheet1, the weekly lesson&class data retrieved from
Sheet5 should be
visible on the B2:AY11 region (I guess a job of using lists&filtering but
don't know the details). A certain cell e.g. AZ11 should also display the
"social activity" for the inputted teacher only.
Regards
J_J




"Tom Ogilvy" wrote in message
...
worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose = True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose = True

Since you don't describe anything that relates teacher to social activity
or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day of
the
week) that can be used throughout the week e.g (Mon1, Mon2, Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2, Fri3,
....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11 will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an
input
cell to accept a teacher name in order to display the weekly program of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly
program
of that class, but having the involved lesson_name+teacher name as
matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

If sheet 5 is already built, then you can just put a pivot table on Sheet 6
and 7

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi Tom,
Thanks for your answer. Sorry for not giving sufficient info.
I was not after the procedures that copies certain rows/columns to Sheet5.
This can partly be done with just copy/paste and filling in cells. The
problem I have is related with Sheet 6 & 7.
On Sheet6 I should be able to input a name, and if that name is the same
as
one of the teachers on Sheet1, the weekly lesson&class data retrieved from
Sheet5 should be
visible on the B2:AY11 region (I guess a job of using lists&filtering but
don't know the details). A certain cell e.g. AZ11 should also display the
"social activity" for the inputted teacher only.
Regards
J_J




"Tom Ogilvy" wrote in message
...
worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose = True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose =
True

Since you don't describe anything that relates teacher to social
activity
or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day
of
the
week) that can be used throughout the week e.g (Mon1, Mon2, Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2, Fri3,
....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11
will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an
input
cell to accept a teacher name in order to display the weekly program
of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly
program
of that class, but having the involved lesson_name+teacher name as
matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

I have not much knowledge on pivot tables. Can you give me some guidience on
implementing this task?

"Tom Ogilvy" wrote in message
...
If sheet 5 is already built, then you can just put a pivot table on Sheet
6
and 7

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi Tom,
Thanks for your answer. Sorry for not giving sufficient info.
I was not after the procedures that copies certain rows/columns to
Sheet5.
This can partly be done with just copy/paste and filling in cells. The
problem I have is related with Sheet 6 & 7.
On Sheet6 I should be able to input a name, and if that name is the same
as
one of the teachers on Sheet1, the weekly lesson&class data retrieved
from
Sheet5 should be
visible on the B2:AY11 region (I guess a job of using lists&filtering
but
don't know the details). A certain cell e.g. AZ11 should also display
the
"social activity" for the inputted teacher only.
Regards
J_J




"Tom Ogilvy" wrote in message
...
worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose =
True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose =
True

Since you don't describe anything that relates teacher to social
activity
or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column
A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working day
of
the
week) that can be used throughout the week e.g (Mon1, Mon2,
Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2,
Fri3,
....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names
of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the AZ2:AZ11
will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have an
input
cell to accept a teacher name in order to display the weekly program
of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the weekly
program
of that class, but having the involved lesson_name+teacher name as
matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

Just walk through the wizard. On the last dialog, hit the layout button and
drag your fields to the places you want to see them. Put the Teacher field
in the Page area and you can use that to select which teacher you want to
see. Pivot tables are specific to numerical data, so you can duplicate one
button in the data area and leave it as count. You can hide that column if
you don't want to see it.

Other than that, it looks like, at least for page 6, you want to copy one
row from page 5 for the designated teach.

If that is the way you want to go, just loop through the teacher column on 5
until the cell matches the cell where the teacher's name would be entered,
then you copy the row and paste it

for each cell in Worksheets("Sheet5").Range("a2:a12")
if cell.Value = Worksheets("Sheet6").Range("A1") then
cell.Entirerow.Copy Destination:=Worksheets("Sheet6").Range("A3")
exit for
end if
Next

--
Regards,
Tom Ogilvy



"J_J" wrote in message
...
I have not much knowledge on pivot tables. Can you give me some guidience
on
implementing this task?

"Tom Ogilvy" wrote in message
...
If sheet 5 is already built, then you can just put a pivot table on
Sheet
6
and 7

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi Tom,
Thanks for your answer. Sorry for not giving sufficient info.
I was not after the procedures that copies certain rows/columns to
Sheet5.
This can partly be done with just copy/paste and filling in cells. The
problem I have is related with Sheet 6 & 7.
On Sheet6 I should be able to input a name, and if that name is the
same
as
one of the teachers on Sheet1, the weekly lesson&class data retrieved
from
Sheet5 should be
visible on the B2:AY11 region (I guess a job of using lists&filtering
but
don't know the details). A certain cell e.g. AZ11 should also display
the
"social activity" for the inputted teacher only.
Regards
J_J




"Tom Ogilvy" wrote in message
...
worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose =
True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose =
True

Since you don't describe anything that relates teacher to social
activity
or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in order
to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4 column
A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working
day
of
the
week) that can be used throughout the week e.g (Mon1, Mon2,
Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2,
Fri3,
....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in
the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the names
of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the
AZ2:AZ11
will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for
each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have
an
input
cell to accept a teacher name in order to display the weekly
program
of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the
weekly
program
of that class, but having the involved lesson_name+teacher name as
matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J

Thank you very much Tom. I' ll follow up the instructions.
Best wishes
J_J

"Tom Ogilvy" wrote in message
...
Just walk through the wizard. On the last dialog, hit the layout button
and
drag your fields to the places you want to see them. Put the Teacher
field
in the Page area and you can use that to select which teacher you want to
see. Pivot tables are specific to numerical data, so you can duplicate one
button in the data area and leave it as count. You can hide that column
if
you don't want to see it.

Other than that, it looks like, at least for page 6, you want to copy one
row from page 5 for the designated teach.

If that is the way you want to go, just loop through the teacher column on
5
until the cell matches the cell where the teacher's name would be entered,
then you copy the row and paste it

for each cell in Worksheets("Sheet5").Range("a2:a12")
if cell.Value = Worksheets("Sheet6").Range("A1") then
cell.Entirerow.Copy Destination:=Worksheets("Sheet6").Range("A3")
exit for
end if
Next

--
Regards,
Tom Ogilvy



"J_J" wrote in message
...
I have not much knowledge on pivot tables. Can you give me some
guidience
on
implementing this task?

"Tom Ogilvy" wrote in message
...
If sheet 5 is already built, then you can just put a pivot table on
Sheet
6
and 7

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi Tom,
Thanks for your answer. Sorry for not giving sufficient info.
I was not after the procedures that copies certain rows/columns to
Sheet5.
This can partly be done with just copy/paste and filling in cells.
The
problem I have is related with Sheet 6 & 7.
On Sheet6 I should be able to input a name, and if that name is the
same
as
one of the teachers on Sheet1, the weekly lesson&class data
retrieved
from
Sheet5 should be
visible on the B2:AY11 region (I guess a job of using
lists&filtering
but
don't know the details). A certain cell e.g. AZ11 should also
display
the
"social activity" for the inputted teacher only.
Regards
J_J




"Tom Ogilvy" wrote in message
...
worksheets(1).range("A1:A10").copy _
Destination:=worksheets(5).Range("A2")
worksheets(2).Range("A1").Resize(50,1).Copy
Worksheets(5).Range("B1").PasteSpecial paste:=xlValues, Transpose
=
True
with worksheets(4).
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
rng.copy
Worksheets(5).Range("AZ1").PasteSpecial paste:=xlValues, Transpose
=
True

Since you don't describe anything that relates teacher to social
activity
or
teach to lesson, not much else can be done.

--
Regards,
Tom Ogilvy


"J_J" wrote in message
...
Hi,

I'll try to give as much detail as I can on this problem in
order
to
increase possible solution alternatives.

In a excel workbook, I have 4 Lists on Sheets 1, 2, 3 and 4
column
A:A
respectively.

The list on Sheet1 shows "names of teachers".
The list on Sheet2 shows periods (10 periods for each 5 working
day
of
the
week) that can be used throughout the week e.g (Mon1, Mon2,
Mon3,....,
Mon10), (Tue1, Tue2, Tue3, ....,Tue10), .........., (Fri1, Fri2,
Fri3,
....,
Fri10) totalling to 50 periods.
The list on Sheet3 shows the lesson names+classes to take it (in
the
format
lesson_name & " " & class_name)
And the list on Sheet4 shows "Social Activities" of the school.

Assuming we have 10 teachers only,
On Sheet5 I need to have a matrix like table, having all the
names
of
teachers listed on range A2:A11.
Period label name texts will be shown on row 1 (B1:AY1).
Cell AZ1 will hold the "Social Activities" text, while the
AZ2:AZ11
will
have Social Activity input for each teacher.
Range B2:AZ11 will hold data of "lesson_name+classes" info for
each
teacher,
a blank cell meaning an empty period for that teacher.

So far not so difficult maybe but Here is the hard part for me:
I need to have two report sheets on Sheet6 and Sheet7.
Sheet6 will have a similar layout of Sheet5, only it should have
an
input
cell to accept a teacher name in order to display the weekly
program
of
the
"selected" teacher only.
Similarly, after inputting the class, Sheet7 will display the
weekly
program
of that class, but having the involved lesson_name+teacher name
as
matrix
cell info.

I am looking for a VBA programming solution for this.
Can anyone help me with this?.
Sincerely

J_J