Hi, I have a macro that selects an entire column based on an input box.
I would like to change it so that it selects the entire column based on the value of 1 cell. ie ( Set up tab, cell A1). And then select that column for every worksheet in my workbook ( there's like 20 ). I appreciate any help. thanks.
Please Login or Register to view this content.
Last edited by jcfrancisco; 10-04-2011 at 09:48 AM.
Please Login or Register to view this content.
You can't "select" columns on more than one sheet at a time, but I doubt you need to "select" anything. Once you start using VBA you can stop "selecting" and "activating" things, only human users need to select a cell/column to then act on it. VBA doesn't have to.
Recorded macros show you selecting and activating, but that stuff can (should) be edited back out.
So, once you know what column you are going to process on each sheet, what are you going to do with the column?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I will copy and and paste formulas to the next column and then copy and paste values to the existing column.
Just to give you an idea:
Please Login or Register to view this content.
Hello jcfrancisco, please remember to use code tags in the future whenever you are displaying code. I have added tags to your original post this time.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
Thanks, I'm getting closer. I'm getting type mismatch with the column "W" and comparing it to a number. It happens on the if statement and on the copy statemement. I tried making it a string.
Please Login or Register to view this content.
The variable type LONG is numeric. You're putting the text letter "W" in G1? That won't work. Change this:
to this:Please Login or Register to view this content.
Please Login or Register to view this content.
Now you can put 1, "a" or "A" in G1 and it will equate to 1 for column A.
Ah, now we're talkin! Thank you so much! I've clicked on your scales and buy you a beer when I see you.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Jerry, one thing I'm trying to figure out now is how to have the
Col = Sheets("Setup").Range("g1").Value take 2 characters once the column range goes past Z. ie AA, AB etc. Right now it only takes the 1st character so it will perform the copy paste on column A instead of AA.
New approach:
Please Login or Register to view this content.
you da man once again! Thank You! owe u 2 beers. I'll mark this "solved". Jim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks