Excel/VBA - Copy and Paste separate sections from table to new worksheets

[Telemeister]

Hello there,
I'm a complete novice when it comes to VBA (though I have a fair bit of experience with Excel). I want to know if it is possible to write a code in VBA to automatically create some lists and where I should start.

I run a large course at a university with approximately 2,000 students. At the beginning of the semester I will receive a spreadsheet that has all the details of each students - names, student ids, emails, addresses, tutorial number etc.

I have about 60 separate classes spread across 20 staff members.

Last semester I created a spreadsheet for my staff which allowed them to click a link to go to a sheet which showed them their class list. My stuff loved this, but it meant I had to copy each class from the data, create a new sheet for each class (so class 1 went to a sheet called 'class 1' etc. right up to 60 classes), rename it to the relevant class number, paste the class, then repeat.

I also created a 'home page' which had the list of staff with a link to each class which they could print. There was also a link back to the home page.

My staff loved this - it was much easier for them to use.

Unfortunately, it took me forever to do it (and to top that off, the university updates class lists twice a week because students move around).

I have been working on my VBA skills (well, the lack of) by working my way through some books (currently making my way through Microsoft Excel 2019 VBA and Macros), and want to automate this process - or at least stream line it.

Can anyone point me in the right direction to get me started?

Thank-you so much
Brett

 

[Telemeister]

Hi all,
I've been playing around in Power Query trying to find a way of doing this and haven't come up with anything yet. Anyone got any ideas?

Much appreciated
Brett

 

[alansidman]

You obviously have an idea as to what this should look like and presented a picture. I cannot understand what it is you are looking for. Perhaps you should explain in simple English terms to us as if we were standing in Starbucks waiting to order a coffee. Use your tables as part of your explanation so we can follow. Obviously, due to the lack of response, your explanation and files are not clicking for us. Help us to help you by making your explanations clear and succinct.

 

[Telemeister]

Ok then.

I run a large course at a university with approximately 2000 students. At the beginning of the semester I receive a spreadsheet that has all the student details - names, student IDs, emails, address, and class number.

An example of this table (with private info removed) looks like the following - Table 1

Class List - For MrExcel.xlsx
	A	B	C	D	E
1	Student ID	Given Name	Class No	Day	Start Time
2	45964583	Aaron	9	TUE	19:00
3	45964967	Aarushi Rajnish	5	MON	16:00
4	45965351	Aastha	15	TUE	14:00
5	45965735	Aastha	38	WED	16:00
6	45966119	Abdur Rafi	4	MON	15:00
7	45966503	Ace	24	TUE	18:00
8	45966887	Adam	27	WED	10:00
9	45967271	Adele	12	TUE	12:00
10	45967655	Aditya	17	TUE	15:00
11	45968039	Admir	28	WED	10:00
12	45968423	Adrian	30	WED	11:00
13	45968807	Adriel	12	TUE	12:00
14	45969191	Ahmed	34	WED	14:00
15	45969575	Ahsan	24	TUE	18:00
16	45969959	Aidan	30	WED	11:00
17	45970343	Aidan	34	WED	14:00
18	45970727	Akshay	7	MON	19:00
19	45971111	Alaani	4	MON	15:00
20	45971495	Alanna	41	THU	09:00
21	45971879	Alannah	14	TUE	13:00
22	45972263	Aleha	13	TUE	13:00
23	45972647	Alex	35	WED	15:00
24	45973031	Alexa	1	MON	13:00
25	45973415	Alexander	13	TUE	13:00
26	45973799	Alexander	21	TUE	17:00
27	45974183	Alexander	30	WED	11:00
28	45974567	Alexander	35	WED	15:00
29	45974951	Alexander	38	WED	16:00
30	45975335	Alexandra	2	MON	18:00
31	45975719	Aleyah	41	THU	09:00
32	45976103	Ali	2	MON	18:00
33	45976487	Ali	4	MON	15:00
34	45976871	Ali	16	TUE	14:00
35	45977255	Alicia	34	WED	14:00
36	45977639	Alisha	30	WED	11:00
37	45978023	Alvin	38	WED	16:00
38	45978407	Alyssa	12	TUE	12:00
39	45978791	Alyssa	27	WED	10:00
40	45979175	Amaan	30	WED	11:00
All

As you can see, students are allocated a class which is identified by a class number (column C).

I then sorted by class number so that the above table becomes Table 2 -
Table 2

Class List - For MrExcel.xlsx
	A	B	C	D	E
1	Student ID	Given Name	Class No	Day	Start Time
2	45973031	Alexa	1	MON	13:00
3	45975335	Alexandra	2	MON	18:00
4	45976103	Ali	2	MON	18:00
5	45966119	Abdur Rafi	4	MON	15:00
6	45971111	Alaani	4	MON	15:00
7	45976487	Ali	4	MON	15:00
8	45964967	Aarushi Rajnish	5	MON	16:00
9	45970727	Akshay	7	MON	19:00
10	45964583	Aaron	9	TUE	19:00
11	45967271	Adele	12	TUE	12:00
12	45968807	Adriel	12	TUE	12:00
13	45978407	Alyssa	12	TUE	12:00
14	45972263	Aleha	13	TUE	13:00
15	45973415	Alexander	13	TUE	13:00
16	45971879	Alannah	14	TUE	13:00
17	45965351	Aastha	15	TUE	14:00
18	45976871	Ali	16	TUE	14:00
19	45967655	Aditya	17	TUE	15:00
20	45973799	Alexander	21	TUE	17:00
21	45966503	Ace	24	TUE	18:00
22	45969575	Ahsan	24	TUE	18:00
23	45966887	Adam	27	WED	10:00
24	45978791	Alyssa	27	WED	10:00
25	45968039	Admir	28	WED	10:00
26	45968423	Adrian	30	WED	11:00
27	45969959	Aidan	30	WED	11:00
28	45974183	Alexander	30	WED	11:00
29	45977639	Alisha	30	WED	11:00
30	45979175	Amaan	30	WED	11:00
31	45969191	Ahmed	34	WED	14:00
32	45970343	Aidan	34	WED	14:00
33	45977255	Alicia	34	WED	14:00
34	45972647	Alex	35	WED	15:00
35	45974567	Alexander	35	WED	15:00
36	45965735	Aastha	38	WED	16:00
37	45974951	Alexander	38	WED	16:00
38	45978023	Alvin	38	WED	16:00
39	45971495	Alanna	41	THU	09:00
40	45975719	Aleyah	41	THU	09:00
All

I then need to allocate each class 1 sheet and then copy that class list to the new sheet. The process after sorting is to select all rows for class 1, create a new sheet for class 1, then copy the details from Table 1 into the new sheet which I name class 1 (as below in Table 3). I then repeat this process for each class, which can be up to 60 classes.

Table 3

Class List - For MrExcel.xlsx
	A	B	C	D	E	F
1	Student ID	Given Name	Class No	Day	Start Time
2	45973031	Alexa	1	MON	13:00
3	45973858	David	1	MON	13:00
4	45974685	Denniella	1	MON	13:00
5	45975512	Edward	1	MON	13:00
6	45976339	Elizabeth	1	MON	13:00
7	45977166	Halle	1	MON	13:00
8	45977993	Hatice	1	MON	13:00	Click here to return to homepage
9	45978820	Hiruni Rebecca	1	MON	13:00
10	45979647	Jada	1	MON	13:00
11	45980474	Jasmine	1	MON	13:00
12	45981301	Joshua	1	MON	13:00
13	45982128	Kelsie	1	MON	13:00
14	45982955	Kristy	1	MON	13:00
15	45983782	Kye	1	MON	13:00
16	45984609	Lucas	1	MON	13:00
17	45985436	Lucy	1	MON	13:00
18	45986263	Maia	1	MON	13:00
19	45987090	Matthew	1	MON	13:00
20	45987917	Millar	1	MON	13:00
21	45988744	Nicholas	1	MON	13:00
22	45989571	Patrick	1	MON	13:00
23	45990398	Patrick	1	MON	13:00
24	45991225	Patrick	1	MON	13:00
25	45992052	Rebekah	1	MON	13:00
26	45992879	Robyn	1	MON	13:00
27	45993706	Ronit	1	MON	13:00
28	45994533	Samuel	1	MON	13:00
29	45995360	Shaellym	1	MON	13:00
30	45996187	Son Lan	1	MON	13:00
31	45997014	Thi Phuong Mai	1	MON	13:00
Tutorial 1

I then create a 'homepage' which has the name of the teachers and a hyperlink to their class lists, identified by their class number as in Table 4

Table 4

Class List - For MrExcel.xlsx
	C	D	E
10	Amrita		39
11			41
12	Anna		9
13	Brett		1
14	Graeme		28
15			29
16	Jennifer		27
17			30
18	Joe		14
19			16
20			19
21	Karen		12
22			13
23			22
24	Marina		4
25			3
26	Mojdeh		34
27			35
28			38
29	Muhammad		20
30			21
31			24
32	Peter		32
33			33
34			36
35	Rowenna		11
36			15
37			17
38	Syed		2
39			5
40			7
Cover

Each class' sheet also contains a link back to this 'homepage' as seen in Table 3.

I want to automate as much of this process as I can as it can take hours to do. I have tried looking at Power Query but I don't think Power Query can do what I need it to do (though I am a novice). This leaves me with VBA code. I have been making my way through some training, but I am not at a point where I can tackle a problem like this.

I hope that is succinct and you understand what I am trying to achieve.

Many thanks for your help

 

[alansidman]

I am understanding. Now in your student list, you have classes going to 41 but have only supplied a tutor list of four classes. For example purposes in Power query, I will build a parameter query for only those four tutors. You may then take that example and extrapolate it out. More to come.

 

[Telemeister]

I've found a way to do the copy and paste part - though it involves a bit of mucking around in VBA. I recorded a macro (code below), and then just change the Criteria to the next class number and the "Sheet6" (below) to the next Sheet (Sheet7) and then run the macro again.

This seems to work, though it is a little clumsy

Sub CreateClassList()
'
' CreateClassList Macro
'

'
Sheets.Add
Sheets("Data").Select
ActiveSheet.ListObjects("Students___Tutors").Range.AutoFilter Field:=7, _
Criteria1:="2"
Range("Students___Tutors[#All]").Select
Range("K10").Activate
Selection.Copy
Sheets("Sheet6").Select
ActiveSheet.Paste
Range("A31").Select
Sheets("Data").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("Students___Tutors").Range.AutoFilter Field:=7
Range("G5").Select
End Sub

 

[Telemeister]

This gets me part of the way, and is certainly a lot quicker, but it's not ideal.

Sheets.Add
Sheets("Data").Select
ActiveSheet.ListObjects("Students___Tutors").Range.AutoFilter Field:=7, _
Criteria1:="2"
Range("Students___Tutors[#All]").Select
Range("K10").Activate
Selection.Copy
Sheets("Sheet6").Select
ActiveSheet.Paste
Range("A31").Select
Sheets("Data").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("Students___Tutors").Range.AutoFilter Field:=7
Range("G5").Select

 

[Telemeister]

Ok, I think I have solved the problem (well, as best I can). I created the macro above plus changed the names of the new sheet to Tutorial 1, Tutorial 2 etc. Then I copy and paste this in VBA and called each version Create1() Create 2() etc. I then changed the sheet numbers to Tutorial 1 etc and the selection criteria (Criteria1"1", "2" etc).

I then wrote a run all macro:
Sub RunAll()
Call Create 1
Call Create 2
Call Create 3
(etc)
End Sub.

I assigned the RunAll Macro to a Form control and then just ran everything. Seemed to work.

A little cumbersome, but it got the job done.

If you think of ways of making this more elegant, then I would love to hear about it.

Thanks all
Brett