Hi Jhong

Sounds to me like you need to use a pivot table to report your data - don't
think you need all kinds of lookups...

You can base the pivot on multiple worksheets or you can combine all your
worksheets into one, usin a macro (adding columns where necessary for
business area) and then base the pivot on the combined sheet)

"jhong" wrote:

Please help me, i did the long way already, i used IF condition to
identify the sheet name and loop to look up the value from another
sheet, someone told me that i can do a loop to get all the info i
needed from those sheets and the loop will stop until all the sheets
have been loop already. Please....

Heres my scenario. I have two workbooks;

In Workbook A, the business areas is the sheet name, inside every
sheet is our cost data, cost center code is our reference and we get
the value from the tenth column. This a report submitted to me.

In Workbook B, my report, I need to do a summary of all business areas
(sheet name in Workbook A), one business area = one column and Cost
center code as my reference column also.

Her's my code in Workbook A-

If BusArea = "HELP"

Call CopyRange - - - - -(Copy values from HELP Sheet)
Call Vlookup - - - - - (Loop to get the value from the Help Sheet
copied)

End Sub

My problem is our business areas is 30 (30 sheets also), so it means i
need to do 30 IF scenarios also, and if we have to add new business
areas, which is another sheet I need to do some coding again.


Thanks in Advance!