Copy rows and paste them as columns in Excel with VBA automatically

Question

I am learning to copy and paste with VBA automatically without overwriting data. I managed to get a code to copy from rows and paste them as rows.

Now, I want to copy rows (Same way) but paste them as a column each time. The first line has to start with a date stamp (Each month) and underneath it the amounts. The amounts are being copied from a pivot table which will refresh then each month.

Here is my written code:

Private Sub CommandButton1_Click()

Dim lastrow As Long, ecol As Long

'Stamp from when the data set is (in months)


        If Worksheets("Database").Range("A3").Offset(1, 1) <> "" Then
            Worksheets("Database").Range("A3").End(xlDown).Select
             ActiveCell.Offset(1, 0).FormulaR1C1 = Now
        End If


'To check the last filled line on sheet 'Database_Input'
lastrow = Sheet12.Cells(Rows.Count, 2).End(xlUp).Row

'Copy Paste section
For i = 2 To lastrow
    Sheet12.Cells(i, 2).Copy
        ecol = Sheet14.Cells(3, Columns.Count).End(xlToRight).Offset(0, 1).Column
    ecol = Sheet14.Cells(3, Columns.Count).End
            Sheet12.Paste Destination:=Sheet14.Cells(3, ecol)
Next i

End Sub

It keeps giving me an error on the following section:

For i = 2 To lastrow
    Sheet12.Cells(i, 2).Copy
        ecol = Sheet14.Cells(3, Columns.Count).End(xlToRight).Offset(0, 1).Column
    ecol = Sheet14.Cells(3, Columns.Count).End
            Sheet12.Paste Destination:=Sheet14.Cells(3, ecol)
Next i

Anyone who has an idea how to deal with this? I copied my row --> row code and edited it. Maybe it has to be completely different.

Many thanks!

About your first lines of code, you might benefit from reading **[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)** — Foxfire And Burns And Burns, Oct 23 '19 at 09:56
What is the point of `ecol` in that loop as you don't use it? — SJR, Oct 23 '19 at 09:58
*It keeps giving me an error* What error? Which line? did you try debugging with F8? Your question now is too broad and unclear. Help making more specific question, please see [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) — Foxfire And Burns And Burns, Oct 23 '19 at 09:58
This line: ecol = Sheet14.Cells(3, Columns.Count).End(xlToRight).Offset(0, 1).Columns — ThisMatthijs, Oct 23 '19 at 10:06
It gives the '1004' error of Object not defined. But I can t see if this code even makes sense — ThisMatthijs, Oct 23 '19 at 10:06
It needs to be `Column` at the end. The second ecol line looks like a typo? — SJR, Oct 23 '19 at 10:13
@SJR Thanks and yes, the `en` was a typo. But still, the same line keeps failing. Is the correct correct (regardless of the links to sheets etc..) — ThisMatthijs, Oct 23 '19 at 10:23
Oops it should be `toleft` as we are starting on the right hand column, code amended below. — SJR, Oct 23 '19 at 10:34

SJR · Accepted Answer · 2019-10-23T10:34:13.440

0

You are wanting the Column property of the Range, not Columns.

Also, you can transfer the value directly which is slightly more efficient than copying and pasting.

I have made a semi-educated guess as to desired destination range.

For i = 2 To lastrow
    ecol = Sheet14.Cells(3, Columns.Count).End(xlToleft).Offset(0, 1).Column 'not columns at the end
    Sheet14.Cells(3, ecol).Value = Sheet12.Cells(i, 2).Value
Next i

edited Oct 23 '19 at 10:34

answered Oct 23 '19 at 10:20

SJR

22,986
6
18
26

Afterthought: could you not do all this in one go with a paste special transpose? – SJR Oct 23 '19 at 10:39
The line `ecol = Sheet14.Cells(3, Columns.Count).End(xlToRight).Offset(0, 1).Column` keeps giving me error '1004' but I can t see why :( – ThisMatthijs Oct 23 '19 at 10:42

score 0 · Answer 2 · answered Oct 23 '19 at 10:51

I didn't even look into your code, if what you want is just transpose version of the data, get your data into an array (range.value will give array) just use a loop to transpose and then assign it to a new range. If you want them to contain formula use range.formula instead of value. just be sure to care about relative/absolute references.

Copy rows and paste them as columns in Excel with VBA automatically

2 Answers2