Copy Transpose paste from 1 worksheet to another , matching sheet name

Question

I have a sample of my sheet

A 1 2 3 4 5 6

B 2 3 7 8 9 5

C 4 2 7 9 5 0

I have created Sheets with name "A", "B", "C" (using VBA), howwever what I could not achieve is match Sheet name A to row A and copy Values (1,2,3,4,5,6), transpose it and paste it in sheet A. And i have a huge sheet so i am trying to loop my code so that it reads through A,B,C and so on. Any help will be appreciated. Thanks

Answer 1

Something like

With ActiveSheet
    For Each cell In .Range("A1:" & .Range("A1").End(xlDown).Address)
        .Range(.Range(cell.Address).Offset(0, 1).Address, .Range(cell.Address).End(xlToRight).Address).Copy
        Sheets("Sheet " & cell.Value).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
    Next
End With

Current sheet names to paste to will come out as "Sheet A", "Sheet B" etc If you need "A","B" etc then remove "Sheet " & from above