Solved

Macro - Paste Special using indirect?  Excel VBA

Posted on 2011-02-16
2
575 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Patrick O'Dea
2 Comments
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 500 total points
ID: 34907923
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
 

Author Closing Comment

by:Patrick O'Dea
ID: 34911375
Thanks, perfect
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question