My data looks like following:
| Header A | Data 1 | Data 2 | Data 3 | Data n |
|---|---|---|---|---|
| Header B | Data 1 | Data 2 | Data 3 | Data n |
| Header C | Data 1 | Data 2 | Data 3 | Data n |
And the expected output is :
| Header A | Data 1 |
|---|---|
| Header A | Data 2 |
| Header A | Data 3 |
| Header B | Data 1 |
| Header B | Data 2 |
| Header B | Data 3 |
| Header C | Data 1 |
| Header C | Data 2 |
| Header C | Data 3 |
I am currently using following, but it gives me a pure transpose, instead of required output:
Sub CopyPaste_ValuesTranspose()
Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(“Sheet1”) ‘<< source sheet name
Set ws2 = Sheets.Add(after:=Sheets(Sheets.Count)) ‘<< new Sheet
ws1.Range(“A1”).CurrentRegion.Copy
ws2.Range(“A1”).PasteSpecial xlValues, Transpose:=True
Application.CutCopyMode = False
ws2.Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Any suggestions?
New contributor
Nikhil Bundile is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.