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

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

      1. 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. 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. 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

  3. Dears ,

    I have a question regarding the VBA codes.
    I want to copy data “specific columns” from opened excel file (excel file is attachment in outlook – Name of the file in outlook attachment looks like: 20171011__GAMA_Programi i aprovuar.xlsx and changes everyday ) and to paste transpose them into another workbook on my PC.
    How can I do this automatically with VBA macro (codes) by one Button taking into account change of the file name everyday( (without changing manually the name of the attached file).
    I appreciate your help

    Thanks in advance.
    Best regards

  4. i have to copy a column(Column header = “contract#”) that is in another workbook and this workbook has multiple sheets. we have to check in every sheets of workbook.

  5. Hello,

    I am trying to do the same thing but with rows and I get an error “Pastespecial method of range class failed” Everything works minus the paste, unless I open the second workbook and select a cell.


    Workbooks.Open Filename:=”C:\……x”
    ActiveSheet.Unprotect Password:=”Secret”
    erow = Sheets(“DiscoveredLessons”).Cells(Rows.Count, “A”).End(xlUp).Offset(0).Row
    If erow >= 1 Then erow = erow + 1
    ActiveSheet.Cells(erow, 1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    ActiveSheet.Protect Password:=”Secret”
    ActiveSheet.Unprotect Password:=”190″
    ActiveSheet.Protect Password:=”190″

Leave a Reply to yahya Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.