Paste transposed data to new row

Question

I am trying to create a form in excel in Sheet 1 (named Form) where the data copied from Sheet 1 (Form) is pasted to Sheet 2 (Data).

The form is vertical; however, the data is horizontal.

As such, I am using PasteSpecial.

When I use the macro button to paste and clear the data from the "Form" to the "Data", it is works for the first and second use. On the third use, the data is pasted on the second set of data rather than in a new row.

Sub Submit()  
    '  
    ' Submit Macro  
    '  

    '  
    Range("C2:C14").Select  
    Selection.Copy  
    Sheets("Data").Select  
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
      :=False, Transpose:=True  
    Range("A3").Select  
    Sheets("Form").Select  
    Application.CutCopyMode = False  
    Selection.ClearContents  
    Range("C2").Select  
End Sub

Yes. That is what I want. The form is set vertically. That is from up to down. whereas the data where it is supposed to be pasted is from left to right. I need transpose for that. — Evan Emmanuel, Mar 18 '20 at 21:55
refer [This](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?noredirect=1&lq=1) — Dy.Lee, Mar 19 '20 at 08:02

score 1 · Accepted Answer · answered Mar 18 '20 at 22:04

1

Does this work?

Sub Submit()

Sheets("Form").Range("C2:C14").Copy
Sheets("Data").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues, Transpose:=True

End Sub

You can read this to find out how to avoid using Select/Activate.

answered Mar 18 '20 at 22:04

SJR

22,986
6
18
26

Yes. I replaced `Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A3").Select` with `Sheets("Data").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues, Transpose:=True` – Evan Emmanuel Mar 18 '20 at 22:18

Paste transposed data to new row

1 Answers1