Linking data from all sheets into a dynamic summary sheet.

Hello to everyone.

I try to do something similar to this thread http://www.mrexcel.com/forum/excel-questions/677890-need-create-dynamic-summary-paage.... but slightly more complex.
My knowledge of Excel and VBA are very poor area, and once again I need to ask your help.
So here I come. As in that thread, I want to link some cells with data from all sheets in a Summary sheet.
I have a workbook with multiple worksheets that are important values beginning with cell B1 (the first value).
The following values which I want to link, is in column B9, G9, H9, and odd numbered lines B9, B11, B13, G9, G11, G13, H9, H11, H13 ... etc. .. downward.
I do not know exactly how many rows are, so I need to check which is the last row that contains data in each worksheet.
Next step. Summary sheet.
There is no need for a new one. Mine Summary sheet exists.
All links must be put in order one after another starting with two rows below the last row containing data in the Summary sheet, in columns B, C and D.

The third step consists in copy by placing a check mark (selected by me), the related values in the Summary sheet, one after another, in the first row found free in the same sheet (Summary), starting with B4 downward. I managed to do this step myself but the next phase is more complicated for me.
So I have to put back the results of the following form.
1. I need to automatically find the sheets whose names belong. If the first result contains something, then it belongs to the first value. If the second result contains something, then it belongs to the second value. The results should be put in sheets whose name belongs in their direction, in columns M and N.
Why do I need this? To not do everything manually. I have between 30-40 sheets in a workbook.
So the whole project in general is hard to do for me.
Any help is greatly appreciated. I put a sheet to see exactly for what I want to do.

Thanks in advance.


Cross posted at ozgridforum. No response yet.

It is very complicated ?

After several searching I found here "Create a summary worksheet from all worksheets (with formulas) Ron de Bruin", I think it is perfect for what I want to do.


The following values which I want to link, is in column B9, G9, H9, and odd numbered lines B9, B11, B13, G9, G11, G13, H9, H11, H13 ... etc. .. downward.
I do not know exactly how many rows are, so I need to check which is the last row odd numbered that contains data in each worksheet.
Mine Summary sheet exists.
All links must be put in order one after another starting with two rows below the last row containing data in the Summary sheet, in columns B, C and D.

Some help for rebuilding layout of this macro is highly welcomed.
This is the starting form.

Please Login or Register  to view this content.

Thank you.

If someone wants to help ..
Are there any progress made​​. But missing a detail ...
Please see here my cross posted at ozgridforum.
Thanks to all.

You will need to explain how you arrive at your summary form. Which data goes where.

Hi arlu.

Was my given a code that works fine in my dummy sheet, but does not work in my original sheet.
I have a worksheets with multiple sheets that are important values beginning with cell B1 (the first value).Columns B, G and H and cell B1 are identical in all sheets.
The following values which I want to link in my Summary, start in B9, G9, H9, and odd numbered lines (B9, B11, B13, G9, G11, G13, H9, H11, H13 ... etc. ..) downward.
I do not know exactly how many rows are, so I need to check which is the last row that contains data in each worksheet.

Mine Summary sheet exists.
All links must be put in order one after another starting with two rows below the last row (cell) containing data in the Summary sheet, in columns B, C and D.

The above code (Create a summary worksheet from all worksheets (with formulas) Ron de Bruin) works fine in my original sheet. Should be amended to copy all existing links referred columns.

I attac a photo to see what must be done sheet.

Originally Posted by arlu1201

You will need to explain how you arrive at your summary form. Which data goes where.

If you need more details

I run vba in different sheets.
I noticed that, in workbooks without merged cell and conditional format works well.
I think the problem is merged cells. I read something about this problem but can not solve.
Some advice, solutions.

This is the code provided by jindon from ozgridforum.

Please Login or Register  to view this content.

Is it working for you?

Work fine in my dummy sheet, but does not work in my original sheet. Column B have merged cell in my original sheet.
You can modify the code above:
1. Delete (ignore) blank cells in column B. Maybe this way will work?
2 Delete (ignore) cells B2: B7. I do not need what is in these cells?

Thank you.

Merged cells are more of a problem than a help.

Also it will be good if jindon himself makes the changes. It will be faster than us having to look through each row of code.

Originally Posted by arlu1201

Merged cells are more of a problem than a help.

Also it will be good if jindon himself makes the changes. It will be faster than us having to look through each row of code.

I know, I read about it. But this is a template sheet, I can not change.
I think jindon is tired of me. He did not answer. Anyway it was a great help. If I knew from the beginning, how complicated is when have merged cells ... I warn him.

I think it's still possible to fix.
But I do not know how to implement skip blanks rows or Columns("B").SpecialCells(xlBlanks).EntireRow.Delete (xlUp).
Another a small effort please.

Please Login or Register  to view this content.

You just found out why not to work with merged cells.

So don't make a template with it (with merged cells) !!!!

And make sure you can change it.

Tip => change your problem at the source.

Originally Posted by oeldere

Please Login or Register  to view this content.

You just found out why not to work with merged cells.

So don't make a template with it (with merged cells) !!!!

And make sure you can change it.

Tip => change your problem at the source.

The template sheet .. it's a long story. And I can not change the source. I need to use it as it is.
If you can help me, ok. If not, your suggestion does not help me at all.
The problem still remains.

Please Login or Register  to view this content.

Talk with your boss to change the template.

He / She wants you to work efficient, so if you explain the problem, hopefully he / she understands the problem and let you change the template.

Originally Posted by oeldere

Talk with your boss to change the template.

Oeldere,

Time is the only thing that you can not take back.
Honestly, I do not understand you.
Why? Why do you spend your time with useless tips (in my case).
I already said. I understand that it's complicated. But certainly not impossible.
The problem is exactly as it is. I can not change sheet template.
And there is no boss in the middle. It is for my personal use (but it's made ​​by another person).
If you can not help, there is no problem, but ...

” Don't spend time beating on a wall, hoping to transform it into a door.” -Coco Chanel

This, truly, is a tip.

Thread solved.
See here the solution.

Why did you open a duplicate thread?

Sorry arlu , but this is not a duplicate post. Do not confuse. Have something in common. An summary sheet of all sheets and the data is linked here.
And the other thread is called "Add two more columns, modifying an existing VBA code help." I have not managed to change ...
In my opinion it is not duplicated. Just similar, but quite different.
And consider, much better the VBA offered by zbor in this thread.