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

Telemeister

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, please share the sheets to understand the data and will help you to create VBA.
 

Telemeister

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
ABCDE
1Identifing CodeNameClass NoDayStart Time
246468277Albekaa9TUE19:00:00
346436545Barrett9TUE19:00:00
446039058Cerro9TUE19:00:00
546402330Daly9TUE19:00:00
646485260Flynn9TUE19:00:00
745969051Green9TUE19:00:00
846359427Karsten9TUE19:00:00
945966885Khan9TUE19:00:00
1046350632Khan9TUE19:00:00
1146474692King9TUE19:00:00
1246459669Kissane9TUE19:00:00
1345960658Le9TUE19:00:00
1445423202Lee9TUE19:00:00
1546013083Lekkas9TUE19:00:00
1646359575McClean9TUE19:00:00
1746397906Moda9TUE19:00:00
1844878559Nirala9TUE19:00:00
1946354182Pellegrini9TUE19:00:00
2046185674Purohit9TUE19:00:00
2146376682Scott9TUE19:00:00
2246549595Sian9TUE19:00:00
2345236631Vuong9TUE19:00:00
2443658709Worsley9TUE19:00:00
2545961697Xiong9TUE19:00:00
2643856667Zhu9TUE19:00:00
2746387412Albano11TUE12:00:00
2846386343An11TUE12:00:00
2945986010Arena11TUE12:00:00
3045212406Atwal11TUE12:00:00
3146386866Ayyash11TUE12:00:00
3245650950Bahl11TUE12:00:00
3345943095Bish11TUE12:00:00
3446370919Choudhury11TUE12:00:00
3546544941Christie11TUE12:00:00
3645640793Dudley11TUE12:00:00
3746386637Kouvchinov11TUE12:00:00
3845448930Kutalyov11TUE12:00:00
3945808430Lai11TUE12:00:00
4045931194Lesui11TUE12:00:00
4146353607Liu11TUE12:00:00
4245908583Mackay11TUE12:00:00
4346371907Manookian11TUE12:00:00
4445797846Mok11TUE12:00:00
4545952396Pascale11TUE12:00:00
4646367128Pereira11TUE12:00:00
4746390871Raad11TUE12:00:00
4846461884Raveen11TUE12:00:00
4945910162Shah11TUE12:00:00
5045900124Singh11TUE12:00:00
Sheet2
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,171
Office Version
  1. 365
Platform
  1. Windows
If this is your source data, what does your mocked up solution look like using this same data.
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Data page.PNG
    Data page.PNG
    75.8 KB · Views: 9
  • Landing Page.PNG
    Landing Page.PNG
    20.9 KB · Views: 9

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,171
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
DE
3Tutor NameClass No
4Michael1
5Peta2
6Sarah3
7Amrita4
Landing


Book2
ABCDE
1IDNameClass NoDayStart
246393072Archibald1MON13:00
346393215Bilson1MON13:00
446393358Bingham1MON13:00
546393501Binsted1MON13:00
646393644Brims-Fleming1MON13:00
746393787Challita1MON13:00
846393930****s1MON13:00
946394073Dang1MON13:00
1046394216Dang1MON13:00
1146394359Deang1MON13:00
1246394502Dsouza1MON13:00
1346394645Durban1MON13:00
1446394788Elwin1MON13:00
1546394931Finselbach1MON13:00
1646395074Fordred1MON13:00
1746395217Graham1MON13:00
1846395360Holmes1MON13:00
1946395503Hook1MON13:00
2046395646Jones1MON13:00
2146395789Lee1MON13:00
2246395932Mcewen1MON13:00
2346396075Nersessian1MON13:00
2446396218Pleass1MON13:00
2546396361Sahiner1MON13:00
2646396504Stevenson1MON13:00
2746396647Sullivan1MON13:00
2846396790Swientek1MON13:00
2946396933Tolhurst Peden1MON13:00
3046397076Woodhill1MON13:00
3146397219Woodward1MON13:00
Tut 1


Book2
ABCDE
1IDNameClass NoDayStart
246429887Abou Abdallah2MON18:00
346430059Austin2MON18:00
446430231Chajju2MON18:00
546430403Chand2MON18:00
646430575Corapovski2MON18:00
746430747Davey2MON18:00
846430919Davidson2MON18:00
946431091Gunawardana2MON18:00
1046431263Hasan2MON18:00
1146431435He2MON18:00
1246431607Kanawati2MON18:00
1346431779Kelly2MON18:00
1446431951Krust2MON18:00
1546432123Kwak-Fang2MON18:00
1646432295Lam2MON18:00
1746432467Li2MON18:00
1846432639Murray2MON18:00
1946432811O'connell2MON18:00
2046432983Park2MON18:00
2146433155Roberts2MON18:00
2246433327Sausau2MON18:00
2346433499Seo2MON18:00
2446433671Singh2MON18:00
2546433843Sorensen2MON18:00
2646434015Thandi2MON18:00
2746434187Vasi2MON18:00
2846434359Vassallo2MON18:00
2946434531Wares2MON18:00
3046434703Worsley2MON18:00
3146434875Younas2MON18:00
Tut 2


Book2
ABCDE
1IDNameClass NoDayStart
246442239Bendall3MON14:00
346442522Carino3MON14:00
446442805Champoux3MON14:00
546443088Coas Filho3MON14:00
646443371Duncan3MON14:00
746443654Duong3MON14:00
846443937Fitzalan3MON14:00
946444220Ha3MON14:00
1046444503Haria3MON14:00
1146444786Henstock3MON14:00
1246445069Hoang3MON14:00
1346445352Huang3MON14:00
1446445635Humphrey3MON14:00
1546445918Jamali3MON14:00
1646446201Johari Tehrani3MON14:00
1746446484Johnston3MON14:00
1846446767Karuna Rajapakse Gedara3MON14:00
1946447050Kim3MON14:00
2046447333Nauriyal3MON14:00
2146447616Nguyen3MON14:00
2246447899Penn3MON14:00
2346448182Sam3MON14:00
2446448465Ta3MON14:00
2546448748Tran3MON14:00
2646449031Williams3MON14:00
2746449314Wong3MON14:00
2846449597Yurkowski3MON14:00
2946449880Zeng3MON14:00
3046450163Zhang3MON14:00
3146450446Zheng3MON14:00
Tut 3


Book2
ABCDE
1IDNameClass NoDayStart
245987437Ali4MON15:00
345988652Aoun4MON15:00
445989867Chen4MON15:00
545991082Cole4MON15:00
645992297Do4MON15:00
745993512Donnell4MON15:00
845994727Ebrahimi4MON15:00
945995942Hettigama4MON15:00
1045997157Holdsworth4MON15:00
1145998372Khan4MON15:00
1245999587Koutsoukos4MON15:00
1346000802Kumra4MON15:00
1446002017Lenaz4MON15:00
1546003232Lin4MON15:00
1646004447Lu4MON15:00
1746005662Manning4MON15:00
1846006877Miclat4MON15:00
1946008092Oates4MON15:00
2046009307Palacios4MON15:00
2146010522Parker4MON15:00
2246011737Percival4MON15:00
2346012952Pham4MON15:00
2446014167Richard4MON15:00
2546015382Schneider4MON15:00
2646016597Simmons4MON15:00
2746017812Sinan4MON15:00
2846019027Suleyman4MON15:00
2946020242Tran4MON15:00
3046021457Tuck4MON15:00
3146022672Wadvani4MON15:00
Tut 4
 

Watch MrExcel Video

Forum statistics

Threads
1,127,894
Messages
5,627,498
Members
416,250
Latest member
darius_rebelo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top