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
 

Telemeister

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

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

alansidman

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

New Member
Joined
Feb 5, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDE
1Student IDGiven NameClass NoDayStart Time
245964583Aaron9TUE19:00
345964967Aarushi Rajnish5MON16:00
445965351Aastha15TUE14:00
545965735Aastha38WED16:00
645966119Abdur Rafi4MON15:00
745966503Ace24TUE18:00
845966887Adam27WED10:00
945967271Adele12TUE12:00
1045967655Aditya17TUE15:00
1145968039Admir28WED10:00
1245968423Adrian30WED11:00
1345968807Adriel12TUE12:00
1445969191Ahmed34WED14:00
1545969575Ahsan24TUE18:00
1645969959Aidan30WED11:00
1745970343Aidan34WED14:00
1845970727Akshay7MON19:00
1945971111Alaani4MON15:00
2045971495Alanna41THU09:00
2145971879Alannah14TUE13:00
2245972263Aleha13TUE13:00
2345972647Alex35WED15:00
2445973031Alexa1MON13:00
2545973415Alexander13TUE13:00
2645973799Alexander21TUE17:00
2745974183Alexander30WED11:00
2845974567Alexander35WED15:00
2945974951Alexander38WED16:00
3045975335Alexandra2MON18:00
3145975719Aleyah41THU09:00
3245976103Ali2MON18:00
3345976487Ali4MON15:00
3445976871Ali16TUE14:00
3545977255Alicia34WED14:00
3645977639Alisha30WED11:00
3745978023Alvin38WED16:00
3845978407Alyssa12TUE12:00
3945978791Alyssa27WED10:00
4045979175Amaan30WED11: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
ABCDE
1Student IDGiven NameClass NoDayStart Time
245973031Alexa1MON13:00
345975335Alexandra2MON18:00
445976103Ali2MON18:00
545966119Abdur Rafi4MON15:00
645971111Alaani4MON15:00
745976487Ali4MON15:00
845964967Aarushi Rajnish5MON16:00
945970727Akshay7MON19:00
1045964583Aaron9TUE19:00
1145967271Adele12TUE12:00
1245968807Adriel12TUE12:00
1345978407Alyssa12TUE12:00
1445972263Aleha13TUE13:00
1545973415Alexander13TUE13:00
1645971879Alannah14TUE13:00
1745965351Aastha15TUE14:00
1845976871Ali16TUE14:00
1945967655Aditya17TUE15:00
2045973799Alexander21TUE17:00
2145966503Ace24TUE18:00
2245969575Ahsan24TUE18:00
2345966887Adam27WED10:00
2445978791Alyssa27WED10:00
2545968039Admir28WED10:00
2645968423Adrian30WED11:00
2745969959Aidan30WED11:00
2845974183Alexander30WED11:00
2945977639Alisha30WED11:00
3045979175Amaan30WED11:00
3145969191Ahmed34WED14:00
3245970343Aidan34WED14:00
3345977255Alicia34WED14:00
3445972647Alex35WED15:00
3545974567Alexander35WED15:00
3645965735Aastha38WED16:00
3745974951Alexander38WED16:00
3845978023Alvin38WED16:00
3945971495Alanna41THU09:00
4045975719Aleyah41THU09: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
ABCDEF
1Student IDGiven NameClass NoDayStart Time
245973031Alexa1MON13:00
345973858David1MON13:00
445974685Denniella1MON13:00
545975512Edward1MON13:00
645976339Elizabeth1MON13:00
745977166Halle1MON13:00
845977993Hatice1MON13:00Click here to return to homepage
945978820Hiruni Rebecca1MON13:00
1045979647Jada1MON13:00
1145980474Jasmine1MON13:00
1245981301Joshua1MON13:00
1345982128Kelsie1MON13:00
1445982955Kristy1MON13:00
1545983782Kye1MON13:00
1645984609Lucas1MON13:00
1745985436Lucy1MON13:00
1845986263Maia1MON13:00
1945987090Matthew1MON13:00
2045987917Millar1MON13:00
2145988744Nicholas1MON13:00
2245989571Patrick1MON13:00
2345990398Patrick1MON13:00
2445991225Patrick1MON13:00
2545992052Rebekah1MON13:00
2645992879Robyn1MON13:00
2745993706Ronit1MON13:00
2845994533Samuel1MON13:00
2945995360Shaellym1MON13:00
3045996187Son Lan1MON13:00
3145997014Thi Phuong Mai1MON13: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
CDE
10Amrita39
1141
12Anna9
13Brett1
14Graeme28
1529
16Jennifer27
1730
18Joe14
1916
2019
21Karen12
2213
2322
24Marina4
253
26Mojdeh34
2735
2838
29Muhammad20
3021
3124
32Peter32
3333
3436
35Rowenna11
3615
3717
38Syed2
395
407
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

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

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

ADVERTISEMENT

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

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

Telemeister

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

Watch MrExcel Video

Forum statistics

Threads
1,127,889
Messages
5,627,481
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