Enabling a data move from a user defined column to a pre-defined column

Question

This is my 1st post on this excellent website. I hope you can help me out with a code I am trying to set up. Apologies in advance if this is a repeat but searching the website I dont think there is a similar case.

In a subroutine I determined that a user can select a column. I want it to be in a column format F:F (but the actual column is to be specified by user, so column F is an example). I came up with the following:

Dim rng As Range

On Error Resume Next

Set rng = Application.InputBox(Prompt:="Select the column", Title:="Selecting the column", Type:=8)

On Error GoTo 0

Next, I want the selected column to move to column Z. For this exercise we wrote the following:

Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row).Copy Destination:=Range("Z2")

So I want the data which starts in the column the user selected to move to the column that we specified (currently it is column Z). The data starts from row 2 with row 1 being the header.

I tried to link the 2 above but am not sure how to proceed.

Your input is appreciated.

score 0 · Answer 1 · answered Aug 21 '18 at 12:12

Here you go:

Sub test()

Dim rng As Range

On Error Resume Next

Set rng = Application.InputBox(Prompt:="Select the column", Title:="Selecting the column", Type:=8)

If rng Is Nothing Then Exit Sub

On Error GoTo 0

rng.Copy Destination:=Range("Z:Z")

End Sub

So basically you were on the right track.

score 0 · Accepted Answer · answered Aug 21 '18 at 12:12

The following code gets the column number from the selected column, creates a range from row 2 to the last row in use of that column and copy the content to Col "Z".

  Sub copyCol()
    Dim rng As Range, col As Long
    Set rng = Application.InputBox(Prompt:="Select the column", _ 
                                   Title:="Selecting the column", Type:=8)
    If rng Is Nothing Then Exit Sub    ' User pressed Cancel.      
    col = rng.Column

    With ActiveSheet
        ' Get number of rows in selected column
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, rng.Column).End(xlUp).row 
        ' Copy the selected column, starting at row 2, to Col Z
        If lastRow > 1 then
            .Range(.Cells(2, col), .Cells(lastRow, col)).Copy Destination:=.Range("Z2")
        End If
    End With
End Sub

(Note that with this version, the user just has to select a single cell of that column)

Thanks a lot. This worked. Since this will be inserted as part of an overall code, I just left out the sub piece. — trialanderror, Aug 22 '18 at 06:24

Enabling a data move from a user defined column to a pre-defined column

2 Answers2