Help finding and copying into an array

[Scoobster_doo]

Hi All,

This has got me beat! I can't figure out if it's an activation issue or not but I'm banging my head against a wall. I've got other areas to copy inot an array and pulled successfully however I cant seem to get this one working. Could anybody point out what I may be missing? I think I've gone 'code blind!'

I'm trying to do the following:

1. Search for a title in a cell
2. If found, copy everything under said title into an array via calling a sub
3.If not found msg up and move on.
4. Copy the array into the same worksheet (As a test to see if its captured)

Please Login or Register  to view this content.

Any help would be MASSIVELY appreciated.

[AlphaFrog]

Please Login or Register  to view this content.

You declared the Housetype variable within the Openworkbookandcopy macro. Which means it's only available to that procedure. It's not shared with CaptureSheetData procedure.

Declare Housetype at the very top of the code module to make it public.

Module scope

A variable that is recognized among all of the procedures on a module sheet is called a "module-level" variable. A module-level variable is available to all of the procedures in that module, but it is not available to procedures in other modules. A module-level variable remains in existence while Visual Basic is running until the module in which it is declared is edited. Module-level variables can be declared with a Dim or Private statement at the top of the module above the first procedure definition.


At the module level, there is no difference between Dim and Private. Note that module-level variables cannot be declared within a procedure.

[Scoobster_doo]

Hi AlphaFrog,

That's great tanks, solves one of my issues

Appreciate you taking the time to read my code!

[AlphaFrog]

You're welcome. What are the other issues with the code?

[Scoobster_doo]

Well I've added a little extra (using your advice on public Dims) and it's doing what I tell it too...kinda

In the following section:

Please Login or Register  to view this content.

It searches through a workbook I defined earlier fine and it does indeed find the headers I required in 3 separate sheets. However when it executes the 'CallCapturesheetdata' sub, it only copies the column for the last sheet found.

The capture sheet data code is below:

Please Login or Register  to view this content.

There's additional bits for test purposes so it may look a mess.

I'm trying to capture the data from each of the 3 columns on each of the 3 sheets it found the word 'Housenumber' but as stated before it only captures the column on the last found sheet. I may have to try and put them each into a separate array which wouldn't be an issue....it's just getting to that point!

Thanks for your help!

[AlphaFrog]

In your original code, you always pasted the data to Range("C:C"). That would overwrite any previous pasted data. So only the last pasted data would remain.

The code below pastes each sheet's data to the next empty rows in column C

Please Login or Register  to view this content.

[Scoobster_doo]

Hi AlphaFrog,

Thanks again for your help. The concept of copy/paste wouldn't work for me as it needs to be arrays for calling into a different workbook later on.

However form what you're saying in regards to the overwriting (Due to "C:C") could be the issue!. Ill have a tinker with what you have put i.e. rather than copy/paste ill create an array and see if that solves the problem.

Thanks again!

[Scoobster_doo]

I can't seem to get it working :/.

Is there a way to keep what were capturing (From the 3 sheets) and then paste the array to a single column...not overwriting the previous sheet?

The reason I have to search is because my 4th sheet onwards are always going to be named differently and this code has to be used hundreds of times over to copy old data from various sheets onto a new template.

Thanks

[AlphaFrog]

Please Login or Register  to view this content.

The concept of copy/paste wouldn't work for me as it needs to be arrays for calling into a different workbook later on.

You don't have to put the values in an array to copy\paste between workbooks. But if that works for you, more power to ya'.

[Scoobster_doo]

Hey Alpha,

I've always just placed into arrays ha...not sure why just the way I was taught!

The code still seems to be only copying the last sheets column in which it found the word house number. When pasting (Using your method) it still only copies the final sheet and not the other two columns in sheets 6 and 7 under the word house number...this ones a tough one!

I did have an issue with #N/A being copied over in other sections of my work but your way of pasting the array on next count removes that so thank you You accidently helped another area haha

[AlphaFrog]

The code still seems to be only copying the last sheets column in which it found the word house number. When pasting (Using your method) it still only copies the final sheet and not the other two columns in sheets 6 and 7 under the word house number...this ones a tough one!

Did you change anything in the code? If yes, what?

Do you get the "Nope: No House Number" message for sheets 6 and 7?

Can you attach an example file that illustrates the problem?

[Scoobster_doo]

Hi Alpha,

I do get a YES for all 3 sheets stating the search criteria is on them all however it only copies the final sheet with a yes. As Far as I'm aware I didn't change anything however I can re-add the code below.

Below is meant to Grab the columns under the search criteria, no matter how many sheets there are i.e. if there are 3 sheets with a house number column then it should capture all 3 columns. In addition there may be more or less with the header...i.e. if 7 sheets contain the header then I want all 7 columns from the 7 sheets pasted into the 1 array

Please Login or Register  to view this content.

'Once the new template is opened and sheet is selected, this is then meant to paste ALL OFF THE COLUMNS into 1 column under C HOWEVER it only seems to paste the final sheet/column when it searches.

This issue is either only the final column is being captured into the array, or when pasting, something is pasting correctly? I'm assuming it's because only the final search results is being captured.

Please Login or Register  to view this content.

I can't add the files due to there being 3 Excel files involved, one of which is sensitive but hopefully the code is enough?

Does this make more sense ha?

If it can't be done then so be it. I may have to look into a way of searching for the header then placing each column under SEPERATE arrays?

Thanks again.

[Scoobster_doo]

I forgot to add the sub code that captures the column:

Please Login or Register  to view this content.

My thought is that the code is Cycling through the sheets THEN capturing hence why it's allways the final sheet stating YES that captures and not the other YES sheets. Perhaps It needs to

1.Find a sheet with the criteria
2.Activate the capture
3.Search again form that sheet for another YES
4. If Yes then Capture (And repeat)
5. If no then stop.

[AlphaFrog]

You did change the code. You removed the part where it pastes the data.

Please Login or Register  to view this content.

As it loops through the sheets, this only copies the data from one sheet. When the loop continues, it copies data from subsequent sheets but the previous array data is overwritten; not added to the existing array. My original code did a copy\paste for each sheet so it didn't matter if the array was overwritten. It would be a lot easier to code if you just pasted the data from each sheet one at a time like in my original code.

So just open the template before you loop through the sheets and add this line back to the code. Reference the template workbook instead of ThisWorkbook. You don't need to "Select" anything if you qualify the workbook and worksheet.

ThisWorkbook.Sheets("Sheet8").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(MyArray, 1)).Value = MyArray

[Scoobster_doo]

Thank you, I shall give it a go and see if it works.

My concern is the reference to "Sheet 8". I will never know the sheet name when running the code in future i.e. after the first 3 dedicated sheets the following sheet names will ALWAYS be different every time this code is ran by a user hence why I needed the search for header function.
Will that make a difference?

[Scoobster_doo]

Apologies, Just understood what the code did...it was the paste into the new template which is static

Scratch my last comment!

[AlphaFrog]

Deleted and Scratched

[Scoobster_doo]

You sir....are my new hero.

Thank you so much for your time!

I'd rep you more if I could

[Scoobster_doo]

It all works however a issue has cropped up annoyingly :/.

Lets say I have the following Data:

Plot Number: House Number:

Sheet 1: Sheet 1:
1 1
2 2
3 3
4 4
5 5
6

Sheet 2:

1 1
2 2
3 3
4 4
5 5
6 6

Because of the xlup method it pastes as:

1 1
2 2
3 3
4 4
5 5
6 1
1 2
2 3
3 4
4 5
5 6
6

Meaning they no longer marry up come Sheet 2+

It should be

1 1
2 2
3 3
4 4
5 5
6
1 1
2 2
3 3
4 4
5 5
6 6

Is there a way in that code

Please Login or Register  to view this content.

To make it NOT overwrite spaces?

I know I've asked a lot ha but you seem to know what you're doing!

[AlphaFrog]

xlUp on column B and offset 1 column to the right to paste.

Please Login or Register  to view this content.

[Scoobster_doo]

Pasting Issue.jpg

Hey Alpha,

Tweaked a few things and now working on validations which is good Nearly there!

Only thing that crops up on pasting is the above image...it only pastes about 3 pages worth of stuff then doesn't past the others and starts spewing the headers in...any ideas?
Not a massive problem as validations can take out the headers when pasting, it's just the missing info form pages 4 onwards

EDIT: Okay 3 pages was just for one - it seems to past the first few pages correct then if the old doc has say 10 pages to paste, they start to go out of order and items are missed etc.

[Scoobster_doo]

Another example is the following:

example 2a.jpg

example 2b.jpg

Blank columns seem to paste through with unusual headers too...

I've put a row offset in of 3 to split the sheets as seen in the code below:

Please Login or Register  to view this content.

But I cnt see that being the issue!

[AlphaFrog]

I can't follow your description. Are pages suppose to mean sheets? You give pictures of what is wrong as if I'm suppose to know what it should to look like. I don't know what the source data looks like either. I have no idea what "Okay 3 pages was just for one" is suppose to mean. The pics don't have column letters or row numbers. So I have no reference.

[Scoobster_doo]

Okay, Thanks for all your help anyway.