Summary Sheet

[TBears]

Good Day,

I have to build a summary sheet with multiple columns, one with sheet name, the value from each sheet in B35, & B36. My workbook has 465 sheets with another 40 or so to add. I used this macro to build a list, which gave me each sheet name but for the life of me I can't figure out how to and the columns I need for the summary of values.

Please Login or Register  to view this content.

Thanks for any help out here, oh and by the way I didn't write the one above and just started working with macros so I'm still kind of clueless. Todd

[DavidJAllen]

TBears,

The code below loops through worksheets 2 to n in the workbook and lists the output in worksheet(1) (i.e. sheet1). In column A you will get worksheet name, column B value of cell B35 and column C value of cell B36.

Please Login or Register  to view this content.

Regards

David

[TBears]

David,

My worksheets are named as such, I only need values from the B&M CO CS# 3, not B&M CO Breakout CS# 3 and so on. That said, will the macro above still work? But the Breakout sheets don't have values in B35 & B36.


B&M CO CS# HV-A
B&M CO Breakout CS# HV-A
B&M CO CS# HV-B
B&M CO Breakout CS# HV-B
B&M CO CS# 1
B&M CO Breakout CS# 1
B&M CO CS# 2
B&M CO Breakout CS# 2
B&M CO CS# 3
B&M CO Breakout CS# 3

[DavidJAllen]

TBears,

Create a new worksheet and name "Index". The macro below will list cells B35 and B36 from those worksheets that do not contain the string "Breakout".

Please Login or Register  to view this content.

Regards

David

[arlu1201]

TBears,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[TBears]

David,

Thanks that worked great. I have tried to build on it. I have to create 2 reports, which are extensive. There are 25 sheets in the workbook I don't want values from other than the "Breakout" sheets there are a total of 534 sheets in the workbook, I need the values from like 260 of them, and I'm trying to populate the summary sheet, I built the following which I thought would work but it gives the error Next For, I don't know for what? Anyway could you look at this and see what I did wrong?

Please Login or Register  to view this content.

[TBears]

This is the summary I'm Trying to populate.

Capture Summary.PNG

[TBears]

Ok I tried to tweak, now not sure, still have compile error, next without for, do I need the activate at the end of the code?

Please Login or Register  to view this content.

[JBeaucaire]

Try this approach instead, that many IFs is a less efficient way of excluding that many sheets:

Please Login or Register  to view this content.