PDA

View Full Version : Want some idea on transposing function



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

p45cal
07-21-2018, 03:44 PM
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
07-21-2018, 07:25 PM
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
07-21-2018, 07:29 PM
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:friends:

p45cal
07-22-2018, 06:18 AM
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
07-22-2018, 11:48 PM
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!👏👏