Returning to first cell at end of macro

Question

simple question that I can't figure out. I have a basic macro does the below, however I can't get it to return to the start point + one column to the right.

Copies a column starting at B2.
Adds that data to the bottom of the data in Column 1.
Returns to the starting cell and then moves one across.

Looking at the picture, Green is where I start, I then copy the column to orange, and then want to move to the red cell to the right of Green

Sub MOVE_COLUMN_TO_ROW1()

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 2).Range("A1").Select

End Sub

My recommendation would be to start by reading [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Nov 18 '19 at 14:28
Use `Application.Goto Sheets("Sheet1").Range("A1"), Scroll:=True` — GMalc, Nov 18 '19 at 14:46
Isn't that just going to go to the A1 cell unless I define the starting point — Scottyp, Nov 20 '19 at 07:07

score 1 · Accepted Answer · answered Nov 18 '19 at 14:50

Some hints to work with assuming the following:

_{Sample data:}

_Code:

Sub Test()

Dim lr As Long 'Use a variable to capture the last used row
Dim lc As Long 'Use a variable to capture the last used column
Dim x As Long 'Use a variable to loop through all used columns
Dim arr As Variant 'Use a variable to capture values you want to transfer

With Sheet1 'Be, at least, explicit about a worksheet object using its CodeName

    'Getting the last used column from the 1st row using xlToLeft
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column

    'Loop from 2nd column to the last used column
    For x = 2 To lc

        'Catch the last used row in the current column(x)
        lr = .Cells(.Rows.Count, x).End(xlUp).Row

        'Fill the array to use in your data transfer
        arr = .Range(.Cells(2, x), .Cells(lr, x))

        'Catch the last used row in the first column
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row

        'Transpose the found range underneath last used row of column 1
        .Cells(lr + 1, 1).Resize(UBound(arr), 1).Value = arr

    'Continue with the next column
    Next x

End With

End Sub

_Result:

Notice that the code is quite extensive and can be written much more compact, but my goal is/I'm hoping that you'll be able to understand each step of the process this way.

Good luck, happy coding =)

I have copied that code but it doesn't seem to be doing anything. Will it work given I am starting at B2 — Scottyp, Nov 18 '19 at 22:55
Have you written down the correct CodeName for the sheet you want to refer to? @Scottyp — JvdV, Nov 19 '19 at 07:22
Crap sorry I didn't see that, have changed 'sheet1' to 'ActiveSheet'. — Scottyp, Nov 20 '19 at 06:24

Returning to first cell at end of macro

1 Answers1