Want some idea on transposing function

[kenneth1226]

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

[p45cal]

You could try pasting to the same location each time:

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1:Z10").Copy
Workbooks.Open Filename:="C:\Users\kenneth\Desktop\TEST\OUTPUT.xlsx"
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End Sub

but be aware that it copies the whole range and overwrites the previous version. Would that do?

[kenneth1226]

Yes, It would do.
Thanks a lot. I am a newbie to VBA and didn't aware that I can get what I want by deleting the eColumn stff

[kenneth1226]

And here I have one more question. If I want to transpose
SOURCE tab's A1:A10 to OUTPUT tab's row1,
A11:A20 to row2, and so on.
What should I put in codes to do so?
Thanks a lot again

[p45cal]

try:

Sub CommandButton1_Click()()
BlockSize = 10    '<<<you can adjust this.
DestRow = 0 'one less that the destination row.
Set SourceSheet = ActiveSheet
Set DestnWB = Workbooks.Open(Filename:="C:\Users\kenneth\Desktop\TEST\OUTPUT.xlsx")
Set DestnSheet = ActiveSheet
With SourceSheet
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  For rw = 1 To lr Step BlockSize
    DestRow = DestRow + 1
    SourceSheet.Cells(rw, 1).Resize(Application.Min(lr - rw + 1, BlockSize)).Copy
    DestnSheet.Cells(DestRow, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
  Next rw
End With
DestnWB.Close True
Application.CutCopyMode = False
End Sub

[kenneth1226]

try:

Sub CommandButton1_Click()()
BlockSize = 10    '<<<you can adjust this.
DestRow = 0 'one less that the destination row.
Set SourceSheet = ActiveSheet
Set DestnWB = Workbooks.Open(Filename:="C:\Users\kenneth\Desktop\TEST\OUTPUT.xlsx")
Set DestnSheet = ActiveSheet
With SourceSheet
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  For rw = 1 To lr Step BlockSize
    DestRow = DestRow + 1
    SourceSheet.Cells(rw, 1).Resize(Application.Min(lr - rw + 1, BlockSize)).Copy
    DestnSheet.Cells(DestRow, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
  Next rw
End With
DestnWB.Close True
Application.CutCopyMode = False
End Sub

It works. Thanks a lot, bro!👏👏