Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Macro - Paste Special using indirect?  Excel VBA

Posted on 2011-02-16
2
Medium Priority
?
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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