Error 1004: union failes

[Tjordaske]

Hi,
I'm trying to split up an Excel in several excels based on a list of values in another Excel.
Let's show an example:

You see that in the top left Excel there is a list of values. My script starts with the first value 'binnendeuren' and search that value in the first 3 columns of the middle Excel. Each row that has that value in one of its first three columns is copied to a new Exel (bottom right)

Than my script should go to (and I think here it goes wrong) to the next value (grondwerken) and do the same thing again in the middle Excel:

The first step is working well but I think that there is a problem with the loop and/or broken link in which Excel he needs to look up the value and in which Excel he needs to find the rows with that value in the first three columns, because whatever I do I get every time an error. The current error I get is a 1004 error with the 'method union is failed' message:

I hope someone can help?

 

[mohadin]

If rng is nothing then
Set rng=ocell.EntireRow
Else
Set rng=Union(rng,ocell.EntireRow)
End If

 

[Tjordaske]

@mohadin,

Thanks, but my current code still gives an error:

 

[GWteB]

You didn't qualify the Range("A:C") so it's implicit a range of the active sheet.
In case the rng variable refers to a range on another sheet than the active sheet (which cannot be determined at compile time) then VBA errors out in run-time as both ranges (rng & ocell) need to be on the same worksheet.

 

[Tjordaske]

Hi @GWteB,
I understand what you're saying but not how to solve this.
I changed the script (see yelow) to this, but still get's an error (see red)

 

[Alex Blakenburg]

Try changing the line to:-

Set rng = Union(rng, ActiveWorkbook.Worksheets(2).Rows(1))

 

[RoryA]

You also need to set Rng to Nothing before you move to the next iteration of the loop.

 

[Tjordaske]

Hi @RoryA and @Alex Blakenburg,

I've added the 'set rng = nothing' and I changed the line to this

But now it's creating the same Excel file over and over again, so it's not looking to the next value of the top left Excel (see original post) and maybe it's also not looking to the middle Excel as well (also see orginal post) because this is the result:

I was hoping by defining 'currentRange' and 'currentWS' in the beginning of the script that those values should remain fixed also when looping...

 

[RoryA]

Where do you set rng to Nothing? I can't see it.

 

[GWteB]

Hi @Tjordaske , are you able to post ALL of your code using code tags rather than an image?
I suspect your issue is due to the current loop logic.