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()

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
Application.CutCopyMode = False

End Sub

Watch the video on YouTube

7 thoughts on “Copy Data Paste another Workbook Transpose Automatically using Excel VBA

      1. Anand

        Sir… awesome tutorials THANKS 🙂

        Like Bill, I am also facing issues while transposing columns from one Workbook to rows in another Workbook. Tried your methodology but its ending with error 1004.
        Unfortunately, there is no help available online on transposing from horizontal to vertical from different workbooks. Can you pl guide ? It will be of great help to me and all.

  1. Rachit

    Hello Sir
    Above code works great. but my data is in single column so whenever I use this code it transpose data in same row only, If want to Transpose like first eight cells of column in first row, Next eight cells in next row and so on. So please share the code for this.

  2. Pingback: Copy Paste Data in Excel with VBA | Excel VBA Training Videos

  3. yahya

    dear sir
    thank you to every thing that you give us . i have question
    how can i copy that data in rows and what will we do if that cells have result from FX
    thank you


Leave a Reply

Your email address will not be published. Required fields are marked *