Pull certain cells from muliple sheets

[dmkolb]

Hi, new to this forum and hoping someone can clear this up for me.

I have a workbook with several hundred sheets. I want to pull data from the same 18 cells (not grouped together, ie; C19, G10, G29..etc) but in this way if possible.
All sheets C19 are to go into column A, G10 would go into column B and so on.
Also I want sheets with blanks in those cells must be skipped leaving no blanks in the master list.

I have some code from MS that will list the data in Column A but it leaves blanks and column B starts where the end of Column A finishes.
I know this is a lot, but any help would be great!
Thanks

[JLGWhiz]

Probably have to use an array to list your 18 cell addresses, then loop a query to see if they are blank while adding your non blank cells to a variable using the Union method. Example.

Please Login or Register  to view this content.

This is untested so it might have a glitch. Also note the periods in front of the Range in the array statement. They are needed to make the 'With sh' Statement work. Also, the Union method will paste whatever ends up in the variable as contiguous cells horizontally from the anchor cell, no matter which worksheet cell the first range is formed from. ie. If the first cell of the Union came from G29, it would still be posted to column A with the remaining Uion cells immediately to its right.

[FDibbins]

Hi, welcome to the forum

I have a workbook with several hundred sheets.

wow that is probably several hundred sheets too many, why on earth do you need to many?
how do you find anything?

[dmkolb]

FDibbins, it's my family tree! Each sheet goes out 4 generations an then each of the 8 on the right go to another sheet and so on! lol
JLGwhiz I will try your idea when I get a chance, thanks.

[dmkolb]

I get an error here:
rAry = Array(.range("C19"), .range("G10"))
it highlights the word .range

but thanks so far!

[dmkolb]

I found if I take the . out of in front of range, it works.
Now errors at set rng = Union part
I added Application.Union but it wants and argument, argh

[JLGWhiz]

I get an error here:
rAry = Array(.range("C19"), .range("G10"))
it highlights the word .range

but thanks so far!

You might have to move that array statement to inside the 'With sh' statement and leave the periods in front of the Range. Like I said, I didn't test it, just trying to give you an idea of how to do it. I have used similar before, but it has been a while.

[dmkolb]

OK here is the code as I have it now, but when the search hits a blank it stops at rng.copy

Please Login or Register  to view this content.

[JLGWhiz]

The Union can be difficult at times when copying. Let's try a different approach. Might take a little longer to run, but should give better results.

Please Login or Register  to view this content.

[dmkolb]

Seems to work but leaves blanks in column B, but I can work with this. Now I have to add several more cells to the array and it already took 5 minutes for 2 columns, phw. But thanks for all your help

[JLGWhiz]

Seems to work but leaves blanks in column B, but I can work with this. Now I have to add several more cells to the array and it already took 5 minutes for 2 columns, phw. But thanks for all your help

I have had company for the past few days. They are gone now, so if I get time I will try to get a speedier code for you.
Regards, JLG

[JLGWhiz]

this might be a little faster, but not much. With all those sheets, I don't think you will get a really speedy macro.

Please Login or Register  to view this content.

Don't forget to put your actual range references into the Union function.

[dmkolb]

Thanks, I'll try this when I can. Just a thought what if we make each range a separate Module? This way I could run them only if that cell changes?

[JLGWhiz]

I don't really understand what your overall objective is, so I couldn't say whether it is a good idea or not. The macro I suggested is looking at all the sheets and copying from all, or most of them. So this statement

This way I could run them only if that cell changes?

is a little confusing.

[dmkolb]

Sorry I should have said as I add new sheets, as this Family tree isn't complete yet. I'm adding several new sheets a day.
But I will go with what you offered for now, I may revisit this idea later, when I have it completed.
Thanks again!