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!

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!

Hi,

Thanks for the reply, actually i never thought the idea of pivot. The
values im getting is a cost sharing percentage which later im going to
multipy from certain values. I want to do the loop to cover those
business area sheets and get those percentage values. Each business
areas will be transferred in separate column. I'll make another
scenarion below maybe you can help me.


Workbook
A
Workbook B

Col Code | % Code |
% Code |
CostA | CostB - - - more column follows Code |
%

Row xx 80% yy
70% xx
80%

yy 70%

Sheets SheetCostA SheetCostB ---More sheet
follows
SheetsSummary
Ws1Sheet


This is what the procedure do.....
- If X = SheetsCostA - - - This is simultaneous, i create an if
scenario for all sheets
- Copy the values from WorkbookA.SheetsCostA to WorkbookB.Ws1Sheet
- Do a lookup from SheetsSummary to Ws1 to get the % to CostA column
- so on.......

Im deadling with 30 sheets and still adding up, i wonder if i can
create a loop that will get all the data from workbookA without doing
an IF condition. My problem is if they add another sheet I need to do
coding again for that sheet, i want my program to be continous.

Any idea will be a great help. Thanks in advance..

















On Jan 25, 5:53*pm, DomThePom
wrote:
Hi Jhong

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

You can base the pivot on multiple worksheets or you can combineallyour
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 andloopto look up the value from another
sheet, someone told me that i can do aloopto getallthe info i
needed from thosesheetsand theloopwill stop untilallthesheets
have beenloopalready. 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 ofallbusiness 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 *- - - - -(Copyvalues from HELP Sheet)
Call Vlookup - - - - - (Loopto get the value from the Help Sheet
copied)

End Sub

My problem is our business areas is 30 (30sheetsalso), 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!- Hide quoted text -

- Show quoted text -