Feb 18 2022 08:47 PM
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:
Refer future state screenshot:
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!
Feb 18 2022 10:57 PM