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
How to copy data, paste it in another workbook while transposing the pasted data using VBA.
Do you have the VBA code using Rows instead of columns
http://www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
Have a look at this link: http://www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
There are many more such examples using rows instead of columns.
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.
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.
how to open excel open password if we forgot. i lost my password excel open.(.xls – format)
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
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
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.
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.
ActiveSheet.Range(“LL_Data”).Copy
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”
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveSheet.Unprotect Password:=”190″
Sheets(“Sheet1”).Range(“LL_Data”).ClearContents
ActiveSheet.Protect Password:=”190″
ActiveWorkbook.Save