kenneth1226
07-20-2018, 05:45 AM
Hi all,
I referred to an old thread
21506-Solved-Transpose-column-A-to-rows
and developed two excel workbooks
One called "SOURCE" and another one called "OUTPUT"
With the following code in the "SOURCE" workbook, I can transpose the data from "SOURCE" workbook to "OUTPUT" workbook and from row to column.
However, I have a problem
When the SOURCE workbook is blank, I write something in A1:B10 and run the command, the data will be transpose normally to B1:K2 in OUTPUT workbook
However, when the new data comes to the column C, I run the command again, both the old and new data will be transpose to L1:U3 in the OUTPUT workbook. As a result there will be old data B1:K2 adnd new data L1:U3 shown up simultaneously.
Here I only want the new data be transposed to B3:K3
What should I modified to get the result? Thanks a lot!
Private Sub CommandButton1_Click()
ActiveSheet.Range("A1:Z10").Copy
Workbooks.Open Filename:="C:\Users\kenneth\Desktop\TEST\OUTPUT.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
I referred to an old thread
21506-Solved-Transpose-column-A-to-rows
and developed two excel workbooks
One called "SOURCE" and another one called "OUTPUT"
With the following code in the "SOURCE" workbook, I can transpose the data from "SOURCE" workbook to "OUTPUT" workbook and from row to column.
However, I have a problem
When the SOURCE workbook is blank, I write something in A1:B10 and run the command, the data will be transpose normally to B1:K2 in OUTPUT workbook
However, when the new data comes to the column C, I run the command again, both the old and new data will be transpose to L1:U3 in the OUTPUT workbook. As a result there will be old data B1:K2 adnd new data L1:U3 shown up simultaneously.
Here I only want the new data be transposed to B3:K3
What should I modified to get the result? Thanks a lot!
Private Sub CommandButton1_Click()
ActiveSheet.Range("A1:Z10").Copy
Workbooks.Open Filename:="C:\Users\kenneth\Desktop\TEST\OUTPUT.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