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

[nickels]

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

 

[HalfAce]

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]

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]

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]

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]

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.

 

[HalfAce]

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!)

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]

Hello HalfAce
Thx for being such a good sport .
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

 

[nickels]

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

 

[nickels]

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