 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006VB in Excel
What I have is a list of names, and a value beside it. Each name has it's own sheet within the workbook. I'm trying to call a macro/VB script in order for it to go to the worksheet I need, pull the info and put it into the value beside the name in the list. If I hard code the name itself, I can get it to go to the worksheet, pull the information I need and put it back on the first worksheet. But how can I say "Go to the worksheet with the name in this cell"? Sheets("Nicole").Activate - works Sheets(ActiveCell.Value).Activate - is what I want to do - but it's not working. What's in the "quotes" I want to populate with the name in the cell. How do I do this? CAN I do this, or am I just crazy? :) Any help will be appreciated. Have you tried using the indirect worksheet function?  SignatureHTH... Jim Thomlinson > What I have is a list of names, and a value beside it. Each name has it's > own sheet within the workbook. I'm trying to call a macro/VB script in order [quoted text clipped - 9 lines] > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > appreciated. No... didn't know there was one. How does that work (I'll do a search in the meantime) > Have you tried using the indirect worksheet function? > [quoted text clipped - 11 lines] > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > appreciated. In Cell A1 put Sheet1 or Sheet2. this formula will return the value from Cell A1 on that sheet. What it does is allows you to build a Sheet!cell address that you want to reference... =indirect(A1 & "!A1") SignatureHTH... Jim Thomlinson > No... didn't know there was one. How does that work (I'll do a search in the > meantime) [quoted text clipped - 14 lines] > > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > > appreciated. will that work if I'm going to different cells? Meaning, I need it to go to Nicole's sheet, search for a certain date, grab the info on that row and put it back on the main page. I may be doing this complicatedly, but I"m trying to make it do what a boss wants it to do *Grin* It's not possible otherwise LOL > In Cell A1 put Sheet1 or Sheet2. this formula will return the value from Cell > A1 on that sheet. What it does is allows you to build a Sheet!cell address [quoted text clipped - 20 lines] > > > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > > > appreciated. You can do a vlookup with the indirect function as the second parameter. You will need to know which range on which sheet that you are looking at =vlookup(something, indirect(A1 & "!" & A2), 2, false) Where A1 contains the sheet name and A2 contains the range address to look in... SignatureHTH... Jim Thomlinson > will that work if I'm going to different cells? Meaning, I need it to go to > Nicole's sheet, search for a certain date, grab the info on that row and put [quoted text clipped - 26 lines] > > > > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > > > > appreciated. Ok, I'll try that (have to go home now *Grin*). Is there a way as well to reference the date? For instance, if your "something" is TODAY() or YESTERDAY() etc and make TODAY() match a cell that would have 10/20/2006 in it? THanks for your help BTW > You can do a vlookup with the indirect function as the second parameter. You > will need to know which range on which sheet that you are looking at [quoted text clipped - 34 lines] > > > > > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > > > > > appreciated. You could do that...it will work the same as any other Vlookup except that it allows you to build the range that you want to look in. The only caution that I would give you is that this function will ahve a lot of overhead associated with it. It is a volatile function which means it will recalc every time there is a calc and vlookups are relatively slow. you would not want thousands of these formulas... SignatureHTH... Jim Thomlinson > Ok, I'll try that (have to go home now *Grin*). Is there a way as well to > reference the date? For instance, if your "something" is TODAY() or [quoted text clipped - 41 lines] > > > > > > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > > > > > > appreciated. I did a search for it, and as far as I know it's not going to return what I need it to return. I need it to go to "Nicole"s worksheet, or Julien's worksheet, based on the name that's in the cell. THe indirect is causing an error :( > Have you tried using the indirect worksheet function? > [quoted text clipped - 11 lines] > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > appreciated. Try Worksheets(ActiveCell.Text).Select or even Worksheets(ActiveSheet.Range("A1").Text).Select if you know the names are in a specific cell. This is working for me. Just have to make sure that whatever is in the cell doesn't have extra leading or trailing spaces, and you could assure yourself of that with Worksheets(Trim(ActiveCell.Text)).Select One key is to make sure that what is in the cell exactly matches the sheet tab name. I've seen people go nuts over something like this only to find that the sheet name had an extra space or two at the end of it. > What I have is a list of names, and a value beside it. Each name has it's > own sheet within the workbook. I'm trying to call a macro/VB script in order [quoted text clipped - 9 lines] > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > appreciated. Thank you :) That worked! NOw I just have to add another loop and I think I'm done! Thank you both for all your help! > Try > Worksheets(ActiveCell.Text).Select [quoted text clipped - 24 lines] > > How do I do this? CAN I do this, or am I just crazy? :) Any help will be > > appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.