Copy data from one sheet to another based on names and dates

Question

So I have two sheets. Both are rosters. One has dates across the top, names down the left. The other is the opposite. I need to copy the data from the latter to the former. I could hardcode the copy cell to cell, the problem is the amount of staff changes from roster to roster. What would be the best approach to complete this?

Answer 1

Something like this:

Option Explicit

Public Sub CopyTranspose(ByVal prgnSourceTopLeftCell As Excel.Range, ByVal prngDestinationTopLeftCell As Excel.Range)
    Dim lastRow As Long
    Dim lastCol As Long

    With prgnSourceTopLeftCell.Worksheet
        lastRow = .Cells(.Rows.Count, prgnSourceTopLeftCell.Column).End(xlUp).Row
        lastCol = .Cells(prgnSourceTopLeftCell.Row, .Columns.Count).End(xlToLeft).Column
        .Range(prgnSourceTopLeftCell, .Cells(lastRow, lastCol)).Copy
    End With

    prngDestinationTopLeftCell.PasteSpecial xlPasteAll, Transpose:=True

    Application.CutCopyMode = False
End Sub

Public Sub Demo()
    CopyTranspose ThisWorkbook.Worksheets("Sheet1").Cells(1, 1), ThisWorkbook.Worksheets("Sheet2").Cells(1, 1)
End Sub

Edit: I see you did submit nice questions along with code in the past, so I guess it was just getting late :-)