VB code to bypass missing sheet occurance

[thomasbeard]

Hello, I have the following code that will define the name (sConsolidation) of a sheet that needs to be copied from a source workbook (wbSource) to a destination workbook (wbDest).
The problem is that sometimes the defined sheet name cannot be found in the wbSource file which brings up an error message on the 6th line of the code. What I want it to do is close the workbook when this occurs and move on to the next cycle (iRow).
Can anyone provide some code that will do this? Thanks

Tom

For iRow = 1 To rInitiativeList.Rows.Count
sInitiativeList = rInitiativeList.Cells(iRow, 1)
sSourceFile = rInitiativeList.Cells(iRow, 1)
Workbooks.Open Filename:=myVariable & "\" & sSourceFile
Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Worksheets(sConsolidation)
wsSource.Unprotect
Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsSource.UsedRange.Value = wsSource.UsedRange.Value
wsSource.Copy after:=wsDest
Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsDest.Name = sInitiativeList
wsDest.Names.Add "ExpenseTemplateDeleteMe", "=""A"""
wbSource.Close False
Next iRow

 

[Peter_SSs]

Try changing the relevant parts of your code to something like this:

<font face=Courier New><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">Set</SPAN> wsSource = wbSource.Worksheets(sConsolidation)<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">' unprotect and other code</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>wbSource.Close <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Next</SPAN> iRow</FONT>

 

[thomasbeard]

This code works in cases where the name does exist but crashes at the "wsSource.Unprotect" code when it doesn't. Is there a way to get the code to skip to the "wbSource.Close False" line when an error does occur? Thanks

Tom

 

[thomasbeard]

P.S. The error code it comes up with is "Automation Error" when it crashes

 

[Peter_SSs]

This code works in cases where the name does exist but crashes at the "wsSource.Unprotect" code when it doesn't. Is there a way to get the code to skip to the "wbSource.Close False" line when an error does occur? Thanks

Tom

If the worksheet name does not exist, the code should not get to the "wsSource.Unprotect" line, it should skip straight down to the "wbSource.Close False" line, which is what you said you wanted in your original post.

What I want it to do is close the workbook when this occurs and move on to the next cycle (iRow).

Note that my code was not complete, but just trying to indicate where I thought you should try modifying yours. Did you try it? If so, and it still failed, please post the modified code right from "For iRow = 1 To rInitiativeList.Rows.Count" down to "Next iRow"

 

[thomasbeard]

This is the modified code that comes up with the automation error...any thoughts?

For iRow = 1 To rInitiativeList.Rows.Count
sInitiativeList = rInitiativeList.Cells(iRow, 1)
sSourceFile = rInitiativeList.Cells(iRow, 1)
Workbooks.Open Filename:=myVariable & "\" & sSourceFile
Set wbSource = ActiveWorkbook
On Error Resume Next
Set wsSource = wbSource.Worksheets(sConsolidation)
On Error GoTo 0
If Not wsSource Is Nothing Then
wsSource.Unprotect
Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsSource.UsedRange.Value = wsSource.UsedRange.Value
wsSource.Copy after:=wsDest
Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsDest.Name = sInitiativeList
wsDest.Names.Add "ExpenseTemplateDeleteMe", "=""A"""
End If
wbSource.Close False
Next iRow


thanks, Tom

 

[Peter_SSs]

I clearly don't have the definition for "rInitiativeList" nor the folder and workbook structure you have. I used this changed code:

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> iRow = 1 <SPAN style="color:#00007F">To</SPAN> 1<br><SPAN style="color:#007F00">'sInitiativeList = rInitiativeList.Cells(iRow, 1)</SPAN><br><SPAN style="color:#007F00">'sSourceFile = rInitiativeList.Cells(iRow, 1)</SPAN><br><SPAN style="color:#007F00">'Workbooks.Open Filename:=myVariable & "\" & sSourceFile</SPAN><br><br>sConsolidation = "zzz"<br><br><SPAN style="color:#00007F">Set</SPAN> wbSource = ActiveWorkbook<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">Set</SPAN> wsSource = wbSource.Worksheets(sConsolidation)<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>****<SPAN style="color:#007F00">'**********************</SPAN><br>****wsSource.Unprotect<br>****<SPAN style="color:#00007F">Set</SPAN> wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)<br>****wsSource.UsedRange.Value = wsSource.UsedRange.Value<br>****wsSource.Copy after:=wsDest<br>****<SPAN style="color:#00007F">Set</SPAN> wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)<br>****wsDest.Name = sInitiativeList<br>****wsDest.Names.Add "ExpenseTemplateDeleteMe", "=""A"""<br>****<SPAN style="color:#007F00">'**********************</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>wbSource.Close <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Next</SPAN> iRow</FONT>

I don't have a sheet called "zzz" and the code skipped straight over the section bounded by the two '**************** lines and closed my workbook.

Must be something different about your workbook/worksheeets but I'm not sure what. Hopefully somebody with a bit more expertise in this area will drop in on this thread and be able to suggest something else.