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.
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)