Copy/transpose from one sheet to another

MrKemp

New Member
Joined
May 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Good day all

I have a table that contains data of teachers timetables. Columns - Monday to Friday, rows are the periods of teaching. Each teacher's TT is below the next so separated by a blank row.
Screenshot 2021-05-03 at 8.20.39 AM.png


I want to create sheets for Monday to Friday and then copy each teacher's Monday, Tuesday, Wednesday etc to the relevant sheet and transpose it. Each teacher in a new row.

With some help of friends and a lot of googling I have gotten to this point, but this just transposes everything into one row as I initially thought each teacher's TT started every 16th row, but soon realised with merged cells this isn't the case.

I'm stuck.

VBA Code:
Sub TransposeData()
Dim rngDst As Range
Dim rngSrc As Range

    ' set reference to source range
    Set rngSrc = Range("D2:E16")
    
    ' set reference to destination range
    Set rngDst = Sheets("Tuesday").Range("B2")
    
    Do
        rngSrc.Copy
        rngDst.PasteSpecial Transpose:=True
        Set rngDst = rngDst.Offset(1)
        Set rngSrc = rngSrc.Offset(15)
    Loop Until rngSrc.Cells(1, 1).Value = ""
    
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,148,242
Messages
5,745,588
Members
423,963
Latest member
lwilson3

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