Macro - pls help =)

[NeedsExcelHelp4]

Hello everyone... I'm new to making macros and would appreciate any hel
given for the questions / problems below.

In one worksheet (the only worksheet so far created), I have man
columns with different data. One column is called School, anothe
Name, another Semester, another Course, and last is Grade.

I want to create a macro that will look in this one worksheet and:

- create a new worksheet for every school with the name of the schoo
as the worksheet name
In every one of the School worksheets I would like:
- Name of the student written out once (would require merging due t
multiple courses taken by the student), followed by courses he ha
taken in the column to the right of it.
- the courses are listed next to the student and would need to b
seperated by Semester (which would go on top right of the exce
worksheet)


I would love to have a macro to do this all automatically... Thank
for any help that you guys can provide

 

[Tom Ogilvy]

Here is a source of code to get you started:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"NeedsExcelHelp4"

 

[NeedsExcelHelp4]

Thanks! That was really useful...

One other quick question though...

Is there a way to alter that code in such a way that it will go through
all of the AutoFilter combinations automatically and create worksheets
for every selection?

Not even sure if that is possible, but that is what I need...

Thanks again.

 

[Tom Ogilvy]

"Create a new sheet for all Unique values"

in that link should do that.


--
Regards,
Tom Ogilvy


"NeedsExcelHelp4"

 

[NeedsExcelHelp4]

Tom... please bear with me b/c I really have very little experience with
macros...

In this part of the code:

Set ws1 = Sheets("Sheet1") '<<< Change
'Tip : Use a Dynamic range name,
http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

I'm supposed to change which variables? Is the below correct?
"Sheet1" should be the name of the worksheet I'm running the code in.

What do I need to change in the 2nd line of code?

Thanks again for all your help! I really really appreciate it...

 

[Tom Ogilvy]

if your sheet is Sheet1 and your data starts in Cell A1, then nothing. If
not, then adjust to reflect what the sheet name is and where the data is
located (upper left corner)

Is this a one time requirement or something you will need to do over and
over again.

You might be able to get a 95 % solution by using a pivot table with your
data, with school in the pagefield.

Set it up to look as you want.

then right clicking in the School field and selecting show pages. This will
duplicate the page for each school.

--
Regards,
Tom Ogilvy


"NeedsExcelHelp4"

 

[NeedsExcelHelp4]

Thanks Tom