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

 

[Saurabhj]

Hi Brett,

Yes, there are two options:

1. VBA
2. Power Query

PowerQuery is very powerful tool of Excel which can help you to create the query once and when you get the new updated class list, the data will be automatically updated in individual sheets. It would be one time effort but next time it will do all activity automatically.

Through VBA you can perform the same. In that we have to understand the data format in the sheet and desired output. As per description you shared, using loops and if statement you can automate the complete process.

You can check Power Query option. Also I can help you in building VBA if PowerQuery didn't work.

Check this video for Power Query.

Thanks,
Saurabh

 

[Telemeister]

Thanks for the video link, I learnt quite a lot there. Unfortunately I couldn't work out how Power Query could help me. I understand the merging processes, and practiced without any problems, but I effectively want to unmerge a bunch of data into separate worksheets and tables based on a class number.

I can write basic VBA and record macros, but I'm not sure how to approach this task.

Brett

 

[Saurabhj]

Hi, please share the sheets to understand the data and will help you to create VBA.

 

[Telemeister]

Hi Saurbh,

I've created a dummy sheet to show you (normally it contains a lot of personal information)

Thank you so much for the help BTW

ClassListTest.xlsm
	A	B	C	D	E
1	Identifing Code	Name	Class No	Day	Start Time
2	46468277	Albekaa	9	TUE	19:00:00
3	46436545	Barrett	9	TUE	19:00:00
4	46039058	Cerro	9	TUE	19:00:00
5	46402330	Daly	9	TUE	19:00:00
6	46485260	Flynn	9	TUE	19:00:00
7	45969051	Green	9	TUE	19:00:00
8	46359427	Karsten	9	TUE	19:00:00
9	45966885	Khan	9	TUE	19:00:00
10	46350632	Khan	9	TUE	19:00:00
11	46474692	King	9	TUE	19:00:00
12	46459669	Kissane	9	TUE	19:00:00
13	45960658	Le	9	TUE	19:00:00
14	45423202	Lee	9	TUE	19:00:00
15	46013083	Lekkas	9	TUE	19:00:00
16	46359575	McClean	9	TUE	19:00:00
17	46397906	Moda	9	TUE	19:00:00
18	44878559	Nirala	9	TUE	19:00:00
19	46354182	Pellegrini	9	TUE	19:00:00
20	46185674	Purohit	9	TUE	19:00:00
21	46376682	Scott	9	TUE	19:00:00
22	46549595	Sian	9	TUE	19:00:00
23	45236631	Vuong	9	TUE	19:00:00
24	43658709	Worsley	9	TUE	19:00:00
25	45961697	Xiong	9	TUE	19:00:00
26	43856667	Zhu	9	TUE	19:00:00
27	46387412	Albano	11	TUE	12:00:00
28	46386343	An	11	TUE	12:00:00
29	45986010	Arena	11	TUE	12:00:00
30	45212406	Atwal	11	TUE	12:00:00
31	46386866	Ayyash	11	TUE	12:00:00
32	45650950	Bahl	11	TUE	12:00:00
33	45943095	Bish	11	TUE	12:00:00
34	46370919	Choudhury	11	TUE	12:00:00
35	46544941	Christie	11	TUE	12:00:00
36	45640793	Dudley	11	TUE	12:00:00
37	46386637	Kouvchinov	11	TUE	12:00:00
38	45448930	Kutalyov	11	TUE	12:00:00
39	45808430	Lai	11	TUE	12:00:00
40	45931194	Lesui	11	TUE	12:00:00
41	46353607	Liu	11	TUE	12:00:00
42	45908583	Mackay	11	TUE	12:00:00
43	46371907	Manookian	11	TUE	12:00:00
44	45797846	Mok	11	TUE	12:00:00
45	45952396	Pascale	11	TUE	12:00:00
46	46367128	Pereira	11	TUE	12:00:00
47	46390871	Raad	11	TUE	12:00:00
48	46461884	Raveen	11	TUE	12:00:00
49	45910162	Shah	11	TUE	12:00:00
50	45900124	Singh	11	TUE	12:00:00
Sheet2

 

[alansidman]

If this is your source data, what does your mocked up solution look like using this same data.

 

[Saurabhj]

Hi,

1. Is this the only Source data ?
2. What is the expected output from this data? Please share the output data also.

Thanks,
Saurabh

 

[Telemeister]

This is a sample of the source data. The file also contains personal information, which I don't want to publish on a public forum. The file I receive from the university includes all students in the unit - the table I took this from had a total of 846 students, but this can range up to around 2000 individual students (2,000 rows).

I have attached screen shots of what I am trying to do. The first is the worksheet for class 1 - so all students with class no 1 in the table. There is also a hyperlink back to the landing page.

The second is the 'landing page' I created for my staff. Each staff member has their name (removed for privacy) and a hyperlinked number. This takes them to the class list for that class (each class has a number). So Tutor 1 can click 39 and be taken to the class list for that particular class.

As I said, my staff love this, but it takes forever to put together. I have been teaching myself Power Query and VBA over the last week or so, and I thought it would be possible to streamline the work.

I'm happy to do the work myself, I really want to learn more about this, but just need a few pointers to get me started.

Thanks again
Brett

 

[alansidman]

To help us to make this work, you will need to make two files that coordinate with each other. I cannot determine who the tutors are. Dummy up a data page that will be the source for the Landing Page. Dummy Data is good. However, we cannot manipulate data in a picture. Use XL2BB to upload your sample files. Help us to help you. Everyone here is a volunteer and has limited time to help. We do not want to spend our time recreating your files to give you an answer.

 

[Telemeister]

Oh, I'm very sorry. Here is a mock up of the data. The 1st page is the landing page with links to 4 other sheets. The 4 other sheets contain the class lists for the relevant classes

Book2
	D	E
3	Tutor Name	Class No
4	Michael	1
5	Peta	2
6	Sarah	3
7	Amrita	4
Landing

Book2
	A	B	C	D	E
1	ID	Name	Class No	Day	Start
2	46393072	Archibald	1	MON	13:00
3	46393215	Bilson	1	MON	13:00
4	46393358	Bingham	1	MON	13:00
5	46393501	Binsted	1	MON	13:00
6	46393644	Brims-Fleming	1	MON	13:00
7	46393787	Challita	1	MON	13:00
8	46393930	****s	1	MON	13:00
9	46394073	Dang	1	MON	13:00
10	46394216	Dang	1	MON	13:00
11	46394359	Deang	1	MON	13:00
12	46394502	Dsouza	1	MON	13:00
13	46394645	Durban	1	MON	13:00
14	46394788	Elwin	1	MON	13:00
15	46394931	Finselbach	1	MON	13:00
16	46395074	Fordred	1	MON	13:00
17	46395217	Graham	1	MON	13:00
18	46395360	Holmes	1	MON	13:00
19	46395503	Hook	1	MON	13:00
20	46395646	Jones	1	MON	13:00
21	46395789	Lee	1	MON	13:00
22	46395932	Mcewen	1	MON	13:00
23	46396075	Nersessian	1	MON	13:00
24	46396218	Pleass	1	MON	13:00
25	46396361	Sahiner	1	MON	13:00
26	46396504	Stevenson	1	MON	13:00
27	46396647	Sullivan	1	MON	13:00
28	46396790	Swientek	1	MON	13:00
29	46396933	Tolhurst Peden	1	MON	13:00
30	46397076	Woodhill	1	MON	13:00
31	46397219	Woodward	1	MON	13:00
Tut 1

Book2
	A	B	C	D	E
1	ID	Name	Class No	Day	Start
2	46429887	Abou Abdallah	2	MON	18:00
3	46430059	Austin	2	MON	18:00
4	46430231	Chajju	2	MON	18:00
5	46430403	Chand	2	MON	18:00
6	46430575	Corapovski	2	MON	18:00
7	46430747	Davey	2	MON	18:00
8	46430919	Davidson	2	MON	18:00
9	46431091	Gunawardana	2	MON	18:00
10	46431263	Hasan	2	MON	18:00
11	46431435	He	2	MON	18:00
12	46431607	Kanawati	2	MON	18:00
13	46431779	Kelly	2	MON	18:00
14	46431951	Krust	2	MON	18:00
15	46432123	Kwak-Fang	2	MON	18:00
16	46432295	Lam	2	MON	18:00
17	46432467	Li	2	MON	18:00
18	46432639	Murray	2	MON	18:00
19	46432811	O'connell	2	MON	18:00
20	46432983	Park	2	MON	18:00
21	46433155	Roberts	2	MON	18:00
22	46433327	Sausau	2	MON	18:00
23	46433499	Seo	2	MON	18:00
24	46433671	Singh	2	MON	18:00
25	46433843	Sorensen	2	MON	18:00
26	46434015	Thandi	2	MON	18:00
27	46434187	Vasi	2	MON	18:00
28	46434359	Vassallo	2	MON	18:00
29	46434531	Wares	2	MON	18:00
30	46434703	Worsley	2	MON	18:00
31	46434875	Younas	2	MON	18:00
Tut 2

Book2
	A	B	C	D	E
1	ID	Name	Class No	Day	Start
2	46442239	Bendall	3	MON	14:00
3	46442522	Carino	3	MON	14:00
4	46442805	Champoux	3	MON	14:00
5	46443088	Coas Filho	3	MON	14:00
6	46443371	Duncan	3	MON	14:00
7	46443654	Duong	3	MON	14:00
8	46443937	Fitzalan	3	MON	14:00
9	46444220	Ha	3	MON	14:00
10	46444503	Haria	3	MON	14:00
11	46444786	Henstock	3	MON	14:00
12	46445069	Hoang	3	MON	14:00
13	46445352	Huang	3	MON	14:00
14	46445635	Humphrey	3	MON	14:00
15	46445918	Jamali	3	MON	14:00
16	46446201	Johari Tehrani	3	MON	14:00
17	46446484	Johnston	3	MON	14:00
18	46446767	Karuna Rajapakse Gedara	3	MON	14:00
19	46447050	Kim	3	MON	14:00
20	46447333	Nauriyal	3	MON	14:00
21	46447616	Nguyen	3	MON	14:00
22	46447899	Penn	3	MON	14:00
23	46448182	Sam	3	MON	14:00
24	46448465	Ta	3	MON	14:00
25	46448748	Tran	3	MON	14:00
26	46449031	Williams	3	MON	14:00
27	46449314	Wong	3	MON	14:00
28	46449597	Yurkowski	3	MON	14:00
29	46449880	Zeng	3	MON	14:00
30	46450163	Zhang	3	MON	14:00
31	46450446	Zheng	3	MON	14:00
Tut 3

Book2
	A	B	C	D	E
1	ID	Name	Class No	Day	Start
2	45987437	Ali	4	MON	15:00
3	45988652	Aoun	4	MON	15:00
4	45989867	Chen	4	MON	15:00
5	45991082	Cole	4	MON	15:00
6	45992297	Do	4	MON	15:00
7	45993512	Donnell	4	MON	15:00
8	45994727	Ebrahimi	4	MON	15:00
9	45995942	Hettigama	4	MON	15:00
10	45997157	Holdsworth	4	MON	15:00
11	45998372	Khan	4	MON	15:00
12	45999587	Koutsoukos	4	MON	15:00
13	46000802	Kumra	4	MON	15:00
14	46002017	Lenaz	4	MON	15:00
15	46003232	Lin	4	MON	15:00
16	46004447	Lu	4	MON	15:00
17	46005662	Manning	4	MON	15:00
18	46006877	Miclat	4	MON	15:00
19	46008092	Oates	4	MON	15:00
20	46009307	Palacios	4	MON	15:00
21	46010522	Parker	4	MON	15:00
22	46011737	Percival	4	MON	15:00
23	46012952	Pham	4	MON	15:00
24	46014167	Richard	4	MON	15:00
25	46015382	Schneider	4	MON	15:00
26	46016597	Simmons	4	MON	15:00
27	46017812	Sinan	4	MON	15:00
28	46019027	Suleyman	4	MON	15:00
29	46020242	Tran	4	MON	15:00
30	46021457	Tuck	4	MON	15:00
31	46022672	Wadvani	4	MON	15:00
Tut 4