select entire column based on cell value

**jcfrancisco** · 09-22-2011, 11:12 AM

Hi, in my macro, I'm trying to have the macro select an entire column (ie W) based on the cell value my users will input ( ie W). Here is my code with the column value hard coded.

' Macro2 Macro
''
Columns("W:W").Select
Selection.Copy

Any suggestions would be greatly appreciated. Jim

**arlu1201** · 09-22-2011, 11:17 AM

Oh this is very simple. All you need to do is as follows:

Dim UserReply As String

Sub test()
    UserReply = InputBox("Please enter column alphabet", "Column Alphabet")

    Columns(UserReply & ":" & UserReply).Select
    Selection.Copy

End Sub

**jcfrancisco** · 09-22-2011, 04:01 PM

thank you. This works great!!!

**jcfrancisco** · 09-22-2011, 04:29 PM

if I could ask 1 more favor. This works great in my one active worksheet, can I make it do the same thing on all worksheets in my workbook? There's like 20.

**arlu1201** · 09-23-2011, 06:14 AM

Is the column the same in all worksheets? If not, is it fine with you if the user receives 20 input boxes asking for the alphabet?

**jcfrancisco** · 09-26-2011, 09:47 AM

yes the collumn is the same in all worksheets. Now that I think of it, maybe I shld just have the user input the the collumn letter in a "set up" sheet and have the macro take it from there.