I'm a VBA rookie, so sincere apologies if this is a bad post.

I have a macro that will take many years in its current state. I cannot figure out how to implement assignment (or any faster method that avoids copy/paste) with both transposing and range offsets.

Please see below an extract of my very slow code. How can I assign values instead of copy/paste?


Sub Simulation()

Dim shPatA As Worksheet, shChA As Worksheet
Set shPatA = ThisWorkbook.Worksheets("Patient")
Set shChA = ThisWorkbook.Worksheets("Chain")

indexA = 0

Do While indexA < Range("Sim_cycles")

    shChA.Select
    Range("E56").Select
    Range(ActiveCell.Offset(indexA, 0), ActiveCell.Offset(indexA, 12)).Copy
    shPatA.Select
    Range("C3:C15").PasteSpecial xlPasteValues, Transpose:=True

    Range("C16:C58").Copy
    shChA.Select
    Range("AQ57").Select
    Range(ActiveCell.Offset(indexA, 0), ActiveCell.Offset(indexA, 42)).PasteSpecial xlPasteValues, Transpose:=True
      
indexA = indexA + 1

Loop

End Sub