Macro - Paste Special using indirect? Excel VBA

See code below.

Columns("B:B").Select

I do NOT want to Select Column "B" specifically.

I actually want to Specify the column that is indicated by the contents of cell A10.

So, for example, if A10 contains "X" then I want: Columns("X:X").Select
So, for example, if A10 contains "Z" then I want: Columns("Z:Z").Select


How do I do this?

Thanks,

Sub Macro2()
'
' Macro2 Macro
'
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
...
...
...

Open in new window

Patrick O'DeaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
FernandoFernandesConnect With a Mentor Commented:
important notes:
1) avoid using "....Select" and "Selection..." in your code, it will always perform faster
2) Copy and Paste Values is "overrated"... you can just say value = value
3) if the content of A10 is not a valid column name, your code will blow up on your face as there's no validation for it... we can make one though  if you want...
Sub Macro2()
dim strColumn as string
    strColumn = Range("A10").Value
    Columns(strColumn & ":" & strColumn).Value =     Columns(strColumn & ":" & strColumn).Value
End Sub

Open in new window

0
 
Patrick O'DeaAuthor Commented:
Thanks, perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.