Using data in a cell to look in a sheet of the same name

nickels

New Member
Joined
Jul 15, 2004
Messages
26
Wondering if anyone can help me out

I have a list of account codes in a "Summary" sheet. I then have a seperate sheet for each account code. I want the information contained in cell A2 in every account code sheet to be pasted into the summary sheet next to its corressponding account code.

I have around 100 account codes so i don't want to keep clicking back and forth to each cell in every sheet, does anyone know a shortcut where basically i can just fill down and it looks at the account code in column A then goes to the sheet with that name and then gives whatever is in cell "A2" in that sheet into column B?? or some vba code that might work??

thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
How are the individual acount code sheets named? (Are their sheet names by chance the account codes in column A of the Summary sheet?)
If not, how can each sheet be identified as to which account it is for?

Dan
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello nickels.... this should do it for you :wink:

Sub GetAccCodeInfo()
For Each AccCode In ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
On Error Resume Next
AccCode.Offset(0, 1).Value = Sheets(AccCode.Value).Range("A2").Value
Next AccCode
End Sub

ASSUMPTIONS MADE:
1) The summary and account code sheets are in the same workbook
2) The Summary sheet is the active sheet when the code is run
3) Account Codes are Constants ( ie. no formula in cell , just a string)
4) The Account Codes found in Column A of the summary sheet match the names of the sheets that you want to get data from.
 

nickels

New Member
Joined
Jul 15, 2004
Messages
26
Sorry, I thought i had made that clear, yeah i have a list of account codes which are the same as the each sheet is named. For example in column A of the sumary sheet cell A1 has the account code "AAA" this corressponds to sheet "AAA", cell A2 has the account code "BBB" this corressponds to sheet "BBB", and so on.

I then want in the Summary sheet, cell "B1" to equal cell "A2" in Sheet "AAA". cell "B2" to equal cell "A2" in Sheet "BBB". Does that make more sense, I hope?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

OK that's what I thought.
This will do that, as will Nimrod's code which looks much, much more cool in my opinion, and I imagine is more efficient too, but since I already wrote it, here's my idea...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TestSub()
<SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet, i <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ws <SPAN style="color:#00007F">In</SPAN> Worksheets
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> i <SPAN style="color:#00007F">In</SPAN> Sheets("Summary").Range("A1", Range("A65536").End(xlUp))
        <SPAN style="color:#00007F">If</SPAN> Ws.Name = i.Value <SPAN style="color:#00007F">Then</SPAN> i(1, 2).Value = Ws.Range("A2").Value
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">Next</SPAN> Ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

One of these ought to be just what you need. (I'd go with Nimrod's, just because. :wink: )

Hope it helps,
Dan
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello HalfAce:
Sorry if my competitive nature got the best of me but...
Hope you don't mind but I just ran your code for 100 Account Codes . It took 10100 loops to complete. My method took 100 . :wink:

Anyways mate ... I still like lots of your code. (y)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

Nope, don't mind at all. I look forward to your comments. Those along with seeing your code seldom fail to teach me something new and cool. I can see why your's loops only 100 times and why mine does so many more. (Now if I can only retain that information, I'll start coding a little better!) :LOL:

Out of curosity, what are you doing to count the loops, just adding a counter, or is there something more magical involved?

Thanks for taking the time to show me better ways. I really, really do appreciate it when you do.

Dan
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello HalfAce :)
Thx for being such a good sport :cool: .
No I'm not doing anything magical ... just putting a loop counter into the code ....

Sub TestSub()
Dim Ws As Worksheet, i As Range
For Each Ws In Worksheets
For Each i In Sheets("Summary").Range("A1", Range("A65536").End(xlUp))
If Ws.Name = i.Value Then i(1, 2).Value = Ws.Range("A2").Value
LoopCount = LoopCount + 1
Next i
Next Ws
MsgBox LoopCount
End Sub

Anyways ... again thx for being such a good sport about my suggestions, you're a real professional (y)
 

nickels

New Member
Joined
Jul 15, 2004
Messages
26
Thanks guys, it sort of worked, i think i must have some incorrect sheet names because a couple didn't work. But you were both very helpful.

Thanks again :biggrin:
 

nickels

New Member
Joined
Jul 15, 2004
Messages
26
Hey guys

Just a small problem, do either of you know why with Nimrod's code i get 12 errors but with Halface i only get 4.

For some data it looks at the wrong sheet for example, for code 287 it goes to sheet "XUH". Do you have any idea why??

Thanks
 

Forum statistics

Threads
1,148,390
Messages
5,746,432
Members
424,016
Latest member
Shizzl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top