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
Bookmarks