-1

I have 12 columns of data. I am trying to transpose it into 12 columns of data, while keeping the absolute cell references in the formula.

Image describing what I am talking about

I am trying to make a macro that will allow me to select the range I need to execute this action on, and then copy each of the absolute formulas and paste them as is into the first column. How do I make the code start with my selection and not an absolute cell.

This is what I have already

I need to do it for all of my sheet, which requires running this action around 100 times.

Community
  • 1
  • 1
Justin E
  • 1
  • 3
    Please do not post pictures of code. Instead, post the code itself (edit your question) so people can copy/paste it if they want to. – braX Aug 04 '22 at 19:18
  • 0) post actual code, not image as previously mentioned. 1) get rid of .select like this: `Range("E4").FormulaR1C1 = "yourformula"` . this will speed up your code. 2) Some options you can research for macro: `Range("A1").Offset(#,#)`, `Selection.formula = ...`, `Dim RG as range, Set RG = Selection...` If you're trying to run the same thing on multiple sheets, you can use `Dim SH as worksheets, for each SH in thisworkbook.worksheets...` – Cameron Critchlow Aug 11 '22 at 17:59

1 Answers1

1

This can be an example and should help get you where you need to be.

First, you'll want to avoid Select, first and foremost.

Second, you'll want some dimensioned ranges, and you can use application.transpose() to move the items:

Option Explicit

Sub test()
    With Sheets(1)
        Dim sourceRange As Range
        Set sourceRange = .Range(.Cells(1, 1), .Cells(2, 2))
        Dim destinationRange As Range
        Set destinationRange = .Cells(5, 1)
        destinationRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = Application.Transpose(sourceRange)
    End With
End Sub

Note the use of Dot notation is followed through the entire range().

enter image description here

Cyril
  • 6,448
  • 1
  • 18
  • 31