Need to transpose cell references

[ethaneandethaneproducts]

No idea why I'm having so much trouble with this. I have a bunch of horizontal data in Sheet 1, and want to transpose it vertically into Sheet 2 so that I can use it for a pivot table. I don't want the formulas from Sheet 1, which contain if statements and links to other excel files, I just want the cell references to Sheet 1. Essentially, I want a quick way to fill sheet 2 (let's say 1000R x 1000C) with ='Sheet 1'!A1 in A1, ='Sheet 1'!A2 in B1, ='Sheet 1'!B1 in A2 and so on.

I can't just fill a sheet with the cell references, lock them, then paste and transpose that, as locking a range of cells with F4 doesn't work for formulas like ='Sheet 1'!A1

Thank you

 

[ethaneandethaneproducts]

I have answered my own question. Make sure to run it with calculation on manual, otherwise it'll recalculate the sheet for every cell it fixes.

Sub AbsoluteReferences()
Dim c As Range
For Each c In Selection
    c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
Next
End Sub

Stolen from Locking multiple cell reference formulas at once

 

[Joe4]

Make sure to run it with calculation on manual, otherwise it'll recalculate the sheet for every cell it fixes.

Note that you can handle that right in the code, like this:

Sub AbsoluteReferences()
    
    Dim c As Range
    
    Application.Calculation = xlCalculationManual
    
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
    Next
    
    Application.Calculation = xlCalculationAutomatic
       
End Sub

and this tweak may even make it run a little faster:

Sub AbsoluteReferences()
    
    Dim c As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
    Next
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
       
End Sub