Help with Error Traps...

[largeselection]

So I have some code (which I'm sure can be written more elegantly, but we all have to start somewhere haha) and I my error statements don't seem to be doing what I was hoping they would do. Basically, my code copies info from bookA.SheetA to bookB.SheetA and I've repeated it so that it does this for every tab.

So I tried to put in some error buffers in case there is no bookA.SheetC so that it doesn't stop the code from running.

I ran an example and in my example I had two sheets bookA.SheetA, bookA.SheetB. The code successfully copied the info from Sheet A and Sheet B into bookB, and it successfully skipped the code to try and copy Sheet C, but then it fizzled and errored in the code to try and copy Sheet D.

I had initially envisioned that it would hit my on errors and then just go through to the end of the routine.

Here's some code:

Part1:
On Error GoTo Part2:
Windows(obk).Activate
Worksheets("C1").Select

Set oRange = a find statement formula
Range("A1:D10").Select
Selection.Copy

ThisWorkbook.Activate
Sheets("C1").Select
Range("A1").Select

ActiveSheet.Paste

Part 2:
On Error GoTo Part3:
Windows(obk).Activate
Worksheets("C2").Select

Set oRange = a find statement formula
Range("A1:D10").Select
Selection.Copy

ThisWorkbook.Activate
Sheets("C2").Select
Range("A1").Select

ActiveSheet.Paste

Part 3:
On Error GoTo Part4:

etc
etc
etc

Through all parts until the last "Part" where I have

On Error GoTo errorend:


errorend:
Exit Sub

So I envisioned the code going to try Part 1 successfully, then when it goes to part 2 it has the on error goto part 3 first so when it can't find sheet "C2" it errors and goes on to try part 3, which says on error goto part 4 so when it can't find sheet "C3" it goes to part 4, etc, etc, etc until it cycles through to the end and gets to the exit sub.

I guess this does not work though since I keep getting an error.

I'm sure I'm lacking some crucial understanding, but any links/info/direction you can point me in would be much appreciated

 

[Ruddles]

Rather than launching in and finding out half way through that something's missing, you could check that everything you need is in place before you start processing it, and if anything is missing you can issue the user with an error message and go no further.

 

[largeselection]

Hmmm but I need it to keep going, not spring the user an error. Like there may only be sheets 1,2,3, and 5 in the users workbook, but I don't want it to stop running I just want it to skip over the fact that 4 is missing and continue on to 5 then skip the fact that 6-end are missing. The users won't be able to add in the sheets that are missing. Thanks so much for your input!

 

[Ruddles]

Ah, capito! Okay then, how about this: write a function which checks for the presence of the workbook and worksheet and returns True or False depending on whether they're found or not. Then you can keep your ON ERROR code confined to inside the function: if you trigger an error, you return False; otherwise you return True.

In your main code, you call the function and only process the workbook/worksheet if the function's happy that they exist - like so:-

If WbWsExist(obk,"C1") Then
   Windows(obk).Activate
   Worksheets("C1").Select
End If

Here's the function:-

Option Explicit
 
Public Function WbWsExist(Wb As String, Ws As String) As Boolean
 
  ' assume the worst until proven otherwise
  WbWsExist = False
 
  On Error GoTo Trap
 
  Windows(Wb).Activate
  Worksheets(Ws).Select
 
  ' if we've got this far then the Wb and Ws both exist
  WbWsExist = True
 
  Exit Function
 
Trap:
  On Error GoTo 0
 
End Function

(This would go in a general code module.)

Tested and working here. Any good to you?

 

[largeselection]

Ah Grazie!

I was able to use what you gave me.

I changed the function to just look for a sheet since I know the wkbk will always be present because of an earlier piece of code.

Thank you again for your help!

 

[Ruddles]

Piacere!