Copy Data Paste another Workbook Transpose Automatically using Excel VBA

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

Watch the video on YouTube