| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VB 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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
Have you tried using the indirect worksheet function?
-- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
No... didn't know there was one. How does that work (I'll do a search in the
meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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 :( "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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") -- HTH... Jim Thomlinson "justvree" wrote: No... didn't know there was one. How does that work (I'll do a search in the meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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 "Jim Thomlinson" wrote: 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") -- HTH... Jim Thomlinson "justvree" wrote: No... didn't know there was one. How does that work (I'll do a search in the meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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... -- HTH... Jim Thomlinson "justvree" wrote: 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 "Jim Thomlinson" wrote: 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") -- HTH... Jim Thomlinson "justvree" wrote: No... didn't know there was one. How does that work (I'll do a search in the meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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 "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "justvree" wrote: 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 "Jim Thomlinson" wrote: 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") -- HTH... Jim Thomlinson "justvree" wrote: No... didn't know there was one. How does that work (I'll do a search in the meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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... -- HTH... Jim Thomlinson "justvree" wrote: 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 "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "justvree" wrote: 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 "Jim Thomlinson" wrote: 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") -- HTH... Jim Thomlinson "justvree" wrote: No... didn't know there was one. How does that work (I'll do a search in the meantime) "Jim Thomlinson" wrote: Have you tried using the indirect worksheet function? -- HTH... Jim Thomlinson "justvree" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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. "justvree" wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VB in Excel
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! "JLatham" wrote: 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. "justvree" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
| Excel docs not saving as excel docs | Excel Discussion (Misc queries) | |||
| Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
| Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
| Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Linear Mode
