Caputre WB name on error and continue

[Coleman34]

Below is the far too complex code I haven't whittled down yet. I have a routine that performs a copy-past from one workbook to a compiled workbook. Sometimes the source data is bad (ie, the source workbook thinks that 9 contiguous cells are non=contiguous BS error) When an error like this occurs, I want to be able to capture the WB name that popped the error then skip that workbook and continue. At the end I would like a Pop up of errored out WBs. Can this be done give what I have?

Due to the sensitivity of the data, I cannot accurately recreate of share the files. The repetition for Risk A/B/C is due to the risk groups being contiguous in rows, but the same 5 categories...its a mess and i a trying to untagle it.

Please Login or Register  to view this content.

[Norie]

Where do you get the error?

Are you checking for the contigous cells?

PS Couldn't you use a For/Next loop for the worksheets, perhaps with a step of 2?

[Coleman34]

I get the error on this line from VBA.

Please Login or Register  to view this content.

I did not know I could check for contiguous cells. But it seems to be something with just certain workbooks...no merged cells or anything like that, It just thinks they aren't contiguous. I can post the exact error if needed. Would this be overkill if it is a tiny portion of the workbooks?

PS , I could, still working out other bugs before I consolidate...I inherited this mess.

[Norie]

What exactly do you mean the workbook/code thinks the cells aren't contiguous?

Does that line of code cause an error?

Could the problem actually be with the copied range, copyBaseDataFrom?

To check for contiguous cells in a range variable you could use the range's Areas property, if all the cells in the range were contiguous that will return 1.

[Coleman34]

My company DL's a number of workbooks that are parsed out from a large database on a private site. We do not control the source workbooks until they are in our servers. That being said they are all the same layout and format but on some the range for copyDataBaseFrom (which will work on one WB won't work on another due to this contiguous cell issue.

If it helps I have attached screen shots of the error box and the highlighted line it throws from.

Like I said it works for 90% of the WBs for some reason it thinks on certain ones that the data is effed.

Doc1.docx

[Norie]

What ranges do the 2 variables copyBaseDataFrom and copyBaseDataTo refer to when you get the error?

[Coleman34]

the EXACT same ranges they do in other workbooks that work fine "A5" to "C5" and down to the last row with data in it.

Please Login or Register  to view this content.

[Norie]

This should skip the pastespecial if copyBaseData is not a contiguous range and print the name of the workbook the problem was with to the debug window.

Please Login or Register  to view this content.

[Coleman34]

I tried that syntax using both CopyBaseDataFrom and copyBaseDataTo and I get and error that says argument is not optional

[Norie]

There was anothe typo/omission,

Please Login or Register  to view this content.

[Coleman34]

I appreciate the help, still throws the exact same error as before. Meh....i don't want to waste your time

[Norie]

I've got a feeling that error isn't caused by a non-contiguous range though obviously that's what the message says the problem is.

Can you print the address of the 2 ranges to the debug window?

To do that add this just before the PasteSpecial

Please Login or Register  to view this content.

[Coleman34]

kersmack. There is the issue...now to fix it since it works all the other damn times LOL

From Range: $A$5:$C$9 to range $C$2

No idea why this happens only with certsin WBs

[Norie]

Copying/pasting between those 2 ranges shouldn't be a problem.

What happens if you add this just before the copy?

[Coleman34]

I love a good mystery...what happens if I put What before the copy?

[Coleman34]

I love a good mystery...what happens if I put What before the copy?

[Norie]

Oops, sorry about that.

I meant this.

Please Login or Register  to view this content.

[Coleman34]

Same error....tried it before both copyBaseData Variablese

[Norie]

So it's still the same error as the one in the image you posted?

[Coleman34]

Yes It is....Frustratingly, yes