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


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

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

    Reply
  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

    Reply
  4. Palidhje

    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

    Reply

Leave a Reply

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