Macro - pls help =)

[NeedsExcelHelp4]

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

In one worksheet (the only worksheet so far created), I have many columns with different data. One column is called School, another 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 school 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 to multiple courses taken by the student), followed by courses he has taken in the column to the right of it.
- the courses are listed next to the student and would need to be seperated by Semester (which would go on top right of the excel worksheet)


I would love to have a macro to do this all automatically... Thanks 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"
<[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone... I'm new to making macros and would appreciate any help
> given for the questions / problems below.
>
> In one worksheet (the only worksheet so far created), I have many
> columns with different data. One column is called School, another
> 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 school
> 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 to
> multiple courses taken by the student), followed by courses he has
> taken in the column to the right of it.
> - the courses are listed next to the student and would need to be
> seperated by Semester (which would go on top right of the excel
> worksheet)
>
>
> I would love to have a macro to do this all automatically... Thanks
> for any help that you guys can provide!
>
>
> --
> NeedsExcelHelp4
> ------------------------------------------------------------------------
> NeedsExcelHelp4's Profile:
http://www.excelforum.com/member.php...o&userid=31898
> View this thread: http://www.excelforum.com/showthread...hreadid=516298
>

[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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> NeedsExcelHelp4
> ------------------------------------------------------------------------
> NeedsExcelHelp4's Profile:
http://www.excelforum.com/member.php...o&userid=31898
> View this thread: http://www.excelforum.com/showthread...hreadid=516298
>

[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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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...
>
>
> --
> NeedsExcelHelp4
> ------------------------------------------------------------------------
> NeedsExcelHelp4's Profile:
http://www.excelforum.com/member.php...o&userid=31898
> View this thread: http://www.excelforum.com/showthread...hreadid=516298
>

[NeedsExcelHelp4]

Thanks Tom!