VBA copy destination transpose

[henrik2h]

Can't really find an answer, perhaps it is not possible? I am trying the following in VBA.

I have a range A1:AB10 (rng1) on sheet1 (always the same size). I want to copy that and transpose it and place it in sheet2 (rng2).

Then the loop runs again and I am then going to copy rng1 again, transpose it and and place it UNDER the first values on sheet2.

However, I do not want to use copy - pastespecial! Preferably I want the data directly (i.e. rng2(offset) = rng 1) OR using copy destination.

It is already in a loop so no problem having a variable change for an offset-statement. Is it possible to use the copy methods above together with "transpose"? How would that line look like?

like this, if you see what I mean

rng2.offset(j,0). = rng1.transpose

 

[henrik2h]

OK, I might have got around it. However, now my source range is not recalculated. The same values are pasted into the target each time. Any ideas?

' Calculate all leases and copy to Pivot sheet
'
'
    Application.ScreenUpdating = False
    Application.Iteration = False
    Dim i As Integer, j As Integer, Lease_Count As Integer, PivotSource As Range, PivotTarget As Range, PivotSourceTrans As Variant, msg As String
    
    Set PivotTarget = Sheets("Pivot").Range("A8:aj138")
    Set PivotSource = Sheets("Flow_calculation").Range("AC225:FC260")
    PivotSourceTrans = Application.WorksheetFunction.Transpose(PivotSource)
    
    Sheets("Pivot").Range("A8:AJ1000000").ClearContents

     Lease_Count = Sheets("Input_by_premises").ListObjects("tbRentRoll").DataBodyRange.Rows.Count

        
    'Calculate for all leases, one by one
    For i = 1 To Lease_Count
    
    ' Offset target range to put values after each other
    j = (i - 1) * 131
    
    'Choose lease from row i, this should trigger a calculation and new PivotSource
    Sheets("Flow_calculation").Range("b3").Value = i

        
    'Copy cash flow data from lease and add to pivot sheet
    PivotTarget.Offset(j, 0) = PivotSourceTrans
    
  
    Next i

    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True


End Sub

 

[henrik2h]

Putting the PivotSourceTrans = Application.WorksheetFunction.Transpose(PivotSource) inside the loop solved it.