thomasbeard
Board Regular
- Joined
- Oct 19, 2005
- Messages
- 139
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
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