Repeat transpose function for 120+ sheets

Copper Contributor

Hi.

I have a spreadsheet with 120+ worksheets. Customer code and name is mentioned in report format on top of a worksheet - I want to have it transposed for every customer in the first two columns before 'brand'.

 

Refer screenshot for current state:

Iz16_0-1645245530911.png

 

Refer future state screenshot:

Iz16_1-1645245940192.png

 

 

I tried a macro - it works on the first sheet and keeps failing for the rest - the spreadsheet goes into loop and repeats the same task on the first worksheet. Below is the current code I am using:

 

Dim ws As Worksheet
For Each ws In Worksheets

 

Sub Customer()
'

' Customer Macro
'

'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9:B10").Select
Selection.FormulaArray = "=TRANSPOSE(R1C4:R2C5)"
Range("A11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A11:B88").Select
Selection.FillDown

 

Next ws

 

End Sub

 

Kindly guide if name of spreadsheet is required in the code? And any help otherwise is appreciated, since this is one spreadsheet with 120 worksheets, and I have 3 more waiting with 200 sheets each. 

 

Looking forward to solutions!

1 Reply
If every worksheet in the workbook is identically laid out, you could try right clicking on the sheet tab, select "select all sheets". Then, whatever you do to the first sheet will be done to all of the sheets in the workbook.

Be sure to make a backup copy of your workbook, and be sure to ungroup the worksheets when you're done (right click on sheet tab and select ungroup sheets).