Following takes a range & copies, pastes, transposes & links. There doesn't seem a way in vba to do this in 1 go that I've been able to find.
- Is there a more efficient or safer way to do this. Keeping in mind;
-needing to do this for large ranges ie. over 100K cells.
-source & destination are in different worksheets or workbooks. So not the same worksheet.
- What issues if any may exist & how to safeguard.
Sub Foo() 'Example1 Call CopyPaste(Sheet1.Range("C10:D20"), Sheet2.Range("C1")) 'Example2 Dim wbNew As Workbook Set wbNew = Workbooks.Add Call CopyPaste(ThisWorkbook.Sheets(1).Range("C10:D20"), wbNew.Sheets(1).Range("C1")) End Sub Sub CopyPaste(rngSrc As Range, rngDest As Range) Application.ScreenUpdating = False ActiveWorkbook.Sheets.Add.Name = "_wsDummy_Temp_" Dim wsDummy As Worksheet Set wsDummy = ActiveWorkbook.Sheets("_wsDummy_Temp_") rngSrc.Copy wsDummy.Activate wsDummy.Range("A1").Select ActiveSheet.Paste Link:=True Dim vTransposed As Variant Dim rngSrcSrcRng As Range Dim vSrcSrc As Variant Dim rngDummy As Range Set rngDummy = wsDummy.Range("A1") Set rngDummy = rngDummy.Resize(rngSrc.Rows.Count, rngSrc.Columns.Count) rngDummy.Formula = Application.ConvertFormula(rngDummy.Formula, xlA1, xlA1, 1) Set rngSrcSrcRng = rngDummy vSrcSrc = rngSrcSrcRng.Formula vTransposed = Application.Transpose(vSrcSrc) Set rngDest = rngDest.Resize(rngDummy.Columns.Count, rngDummy.Rows.Count) rngDest.Formula = vTransposed rngDummy.ClearContents Application.DisplayAlerts = False wsDummy.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
With the answer provided @TinMan I decided to fill over a 1M cells in a worksheet with numbers & do some benchmarking.
Original OP function: 33 to 39 seconds.
Refactored CopyPaste function: 20 to 26 seconds.
Alternate Approach TransposeLink function: 11 to 13 seconds.
It appears the last one is the fastest in the tests I did but also removes the need to use another temporary worksheet, removes need to use select or the clipboard.