Copy/Paste data using VBA for one sheet to another

[Bstatum]

Hello Excel Community,

I’m currently working on a university class scheduling project and am in need of some VBA coding assistance.

I would like to set up a system that can read data from a “Master” list and copy/paste specific information from the master sheet to another sheet based on certain criteria.
My “Master” list contains full class details (i.e Subject, Room, Time, Trainer, etc)

Once the list is read I would like to copy the subject name into an (already set up) “Day” sheet and paste the name into an intersecting cell that lines up with the current “room” and “time” information from the “Master”. (ex. The Communication (Subject) is in the Main Aud (Room) at 9:15am(Time).

My project is based all in the same workbook as well.

I’m not quite sure if this is possible but I would appreciate any direction or if there is an easy way to achieve the same result.

Thanks for your input
-Bryon

I have included basic examples below.
Year Stream Semester Subject Class Type Tutorial Code Day Start Time End Time Location Room Trainer Certificate Pastoral 2 Communication Core CC2P1 Monday 9:15 AM 11:15 AM Waterloo Main Aud Trainer 1
Certificate Pastoral 2 Personal Leadership Core CC2P1 Monday 11:15 AM 12:45 PM Waterloo Main Aud Trainer 1
Certificate Pastoral 2 Communication Core CC2P2 Monday 12:00 PM 1:00 PM Waterloo Main Aud Trainer 2
Certificate Pastoral 2 Personal Leadership Core CC2P2 Monday 1:00 PM 2:30 PM Waterloo Main Aud Trainer 2



Time Main Aud 2nd Aud Music Room Library 1 Library 2
8:00
8:15
8:30
8:45
9:00
9:15 Communication
9:30
9:45
10:00
10:15 Personal Leadership
10:30
10:45
11:00
11:15
11:30
11:45
12:00 Communication
12:15
12:30
12:45
13:00 Personal Leadership
13:15
13:30
13:45
14:00

 

[Bstatum]

Here are some more clear examples

[TABLE="width: 0"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Stream[/TD]
[TD]Semester[/TD]
[TD]Subject[/TD]
[TD]Class Type[/TD]
[TD]Tutorial Code[/TD]
[TD]Day[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[TD]Room [/TD]
[TD]Trainer 1[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Communication[/TD]
[TD]Core[/TD]
[TD]CC2P1[/TD]
[TD]Monday[/TD]
[TD]9:15 AM[/TD]
[TD]11:15 AM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 1
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Personal Leadership[/TD]
[TD]Core[/TD]
[TD]CC2P1[/TD]
[TD]Monday[/TD]
[TD]11:15 AM[/TD]
[TD]12:45 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud
[/TD]
[TD]Trainer 1
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Communication[/TD]
[TD]Core[/TD]
[TD]CC2P2[/TD]
[TD]Monday[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 2
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Personal Leadership[/TD]
[TD]Core[/TD]
[TD]CC2P2[/TD]
[TD]Monday[/TD]
[TD]1:00 PM[/TD]
[TD]2:30 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 2
[/TD]
[/TR]
</tbody>[/TABLE]

Time	Main Aud	2nd Aud	Music Room	Library 1	Library 2
8:00
8:15
8:30
8:45
9:00
9:15	Communication
9:30
9:45
10:00
10:15	Personal Leadership
10:30
10:45
11:00
11:15
11:30
11:45
12:00	Communication
12:15
12:30
12:45
13:00	Personal Leadership
13:15
13:30
13:45
14:00

<tbody>
</tbody>

 

[Bstatum]

Here are some more clear examples below.
[TABLE="width: 0"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Stream[/TD]
[TD]Semester[/TD]
[TD]Subject[/TD]
[TD]Class Type[/TD]
[TD]Tutorial Code[/TD]
[TD]Day[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[TD]Room [/TD]
[TD]Trainer 1[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Communication[/TD]
[TD]Core[/TD]
[TD]CC2P1[/TD]
[TD]Monday[/TD]
[TD]9:15 AM[/TD]
[TD]11:15 AM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 1
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Personal Leadership[/TD]
[TD]Core[/TD]
[TD]CC2P1[/TD]
[TD]Monday[/TD]
[TD]11:15 AM[/TD]
[TD]12:45 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 1
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Communication[/TD]
[TD]Core[/TD]
[TD]CC2P2[/TD]
[TD]Monday[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 2
[/TD]
[/TR]
[TR]
[TD]Certificate[/TD]
[TD]Pastoral[/TD]
[TD]2[/TD]
[TD]Personal Leadership[/TD]
[TD]Core[/TD]
[TD]CC2P2[/TD]
[TD]Monday[/TD]
[TD]1:00 PM[/TD]
[TD]2:30 PM[/TD]
[TD]Waterloo[/TD]
[TD]Main Aud[/TD]
[TD]Trainer 2
[/TD]
[/TR]
</tbody>[/TABLE]

Time	Main Aud	2nd Aud	Music Room	Library 1	Library 2
8:00
8:15
8:30
8:45
9:00
9:15	Communication
9:30
9:45
10:00
10:15	Personal Leadership
10:30
10:45
11:00
11:15
11:30
11:45
12:00	Communication
12:15
12:30
12:45
13:00	Personal Leadership
13:15
13:30
13:45
14:00

<tbody>
</tbody>

 

[Virici]

Re: Using VBA to Copy/Paste data from one worksheet to another

The copy and paste function you are asking for is definately doable, but you should consider the following before writing an actual code:
1) Is the time-format equal in both sheets or do you need to transform time from AM/PM format to 24H format first?
2) Do you need the day sheet to only show schedule start times or the full schedule frame, meaning that time is filled between 9:15 and 11:15 for a 'Communication' ?
3) Do you need the code to review for overlapping schedules?

 

[Bstatum]

Re: Using VBA to Copy/Paste data from one worksheet to another

Thanks Virici for your reply.

To answer your questions above
1) Yes the time format would remain the same across all sheets
2) It would be great to see the full frame schedule
3) The ability to catch mistakes would be great as well by being able to see any overlapping schedules.

Thank you

 

[Virici]

Re: Using VBA to Copy/Paste data from one worksheet to another

It can be done, but what you're requesting is building several modules that function together to achieve the task. And it will not be simple code, as you will probably need a 'mask' (a matrix of time-slots) on-top of which VBA draws objects to visualize scheduled meetings.

It is thus doable, but you should re-consider the time needed to put into it. There is no easy-solution if you need to see the full frame schedule and especially not if you need to catch mistakes in the booking lists, although such would be handy.
Have you considered the option of combining a new Outlook calendar with VBA and let Outlook handle the booking schedule?

If you're determined to build the program in Excel, I can assist, but it will take time.

 

[Bstatum]

Re: Using VBA to Copy/Paste data from one worksheet to another

Thanks for the tips Virici

Ill keep researching and see if i can figure it out for myself.

 

[Fluff]

Could you please answer the following?
a) Do you have a separate sheet for each day, or do you you have just 1 daily sheet, which you overwrite everyday?
b) In your example the daily sheet is in 24Hr format, whilst the Master is in 12Hr format. Is this how your data is setup?
c) In your example you have Personal Leadership at 11:15 in the Master but 10:15 in the Daily. Is this just a typo?
d) Is it just the subject that you want to copy to the Daily sheet?
e) How are your Days & times entered? Are they text, dates/times with custom formats applied?
f) On both sheets is your header in row 1 starting in A1, with your actual data starting in A2?
g) What should happen if there is an overlap? ie Personal leadership finishes at 12:45 but Communication starts at 12:00

 

[Bstatum]

Thanks Fluff for your reply.
To answer your questions below.

a)Yes each day would have its own seperate sheet with the room names as columns from Column B and the 24 hr times listed in Column A.
b)Yes this was a typo i would prefer the 12 hr time format.
c)as a general example the copy/paste function would read the Master list data and paste over the Subject names to the specified time from the Master. Yes this was probably a typo.
d)Ideally i would like to copy over more information (i.e Time, Room, Subject , Tutorial Code & Trainer) but as a starting point just having the Subject name would work as well.
e)The Days are set as general text and the time is set to a custom h:mm AM/PM time format.
f)The Master list starts A2 and the Day sheets would start B2, to give space for the time column.
g)Ideal these mistakes will be caught manually using the Master list. But if possible it would be great to get a message box pop up for the conflicting Day,Time and subject info.


Thanks for your help. I'm very happy to do the work i just needed a good direction to go to regarding the VBA coding. Or even examples of what the codes would look like

Thanks

 

[Fluff]

My apologies for the delayed reply, unfortunately I must have missed your reply.
As a start try this

Sub TimeSchedule()

   Dim Cl As Range
   Dim Fnd1 As Range
   Dim Fnd2 As Range
   
   For Each Cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
      Set Fnd1 = Sheets(Cl.Value).Range("A:A").Find(CDate(Cl.Offset(, 1).Value), , , , , , , , False)
      Set Fnd2 = Sheets(Cl.Value).Range("A:A").Find(CDate(Cl.Offset(, 2).Value), , , , , , , , False)
      If Not Fnd1 Is Nothing And Not Fnd2 Is Nothing Then
         Select Case Cl.Offset(, 4)
            Case "Main Aud"
               Fnd1.Offset(, 1).Resize(Fnd2.Row - Fnd1.Row + 1).Value = Cl.Offset(, -3).Value
            Case "2nd Aud"
               Fnd1.Offset(, 2).Resize(Fnd2.Row - Fnd1.Row + 1).Value = Cl.Offset(, -3).Value
            Case "Music room"
               Fnd1.Offset(, 3).Resize(Fnd2.Row - Fnd1.Row + 1).Value = Cl.Offset(, -3).Value
         End Select
      End If
   Next Cl
         
End Sub

It assumes that your "Master list" is the active sheet when you run the macro & that your daily sheets are called Monday, Tuesday etc