How to copy data, paste it in another workbook while transposing the pasted data using VBA.
- First select the data
- Next copy it
- Open the workbook in which you wish to paste it
- Find the next empty or blank column
- Select a cell next to the column containing data like headers
- Now paste the data using paste special so that you can also transpose the data
The complete VBA code is given below;
Private Sub CommandButton1_Click()
ActiveSheet.Range(“A2:F4″).Copy
Workbooks.Open Filename:=”C:\Users\takyar\Desktop\copied-employee-data.xlsx”
eColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
If eColumn >= 1 Then eColumn = eColumn + 1
ActiveSheet.Cells(1, eColumn).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End Sub