Error handling question: if sheet doesn't exist, go to

[lynnsong986]

Hello,

I have the codes below to basically copy and paste some data from one workbook (book23) to other workbooks.
the problem is that sheet "WDed15" may not exist. Can someone show me how to add an if statement to test if sheet("WDed15") exists, if it does, continue to execute the entire coding, if it doesn't, go straight to line 13.

1. Workbooks("book23.xlsx").Activate
2. On Error Resume Next
3. ActiveWorkbook.Sheets("WDed15").Activate
4. lastrow = ActiveWorkbook.ActiveSheet.Cells(rows.count, 1).End(xlUp).Row
5. lastcol = ActiveWorkbook.ActiveSheet.Cells(3, Columns.count).End(xlToLeft).Column
6. ActiveSheet.Range(Cells(2, 1), Cells(lastrow, lastcol)).Copy
7. Workbooks("Billing Report 30 Titan.xlsx").Activate
8. lastrow1 = ActiveWorkbook.Worksheets(Worksheets.count).Cells(rows.count, 1).End(xlUp).Row
9. ActiveWorkbook.Worksheets(Worksheets.count).Range("A" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
10. ActiveWorkbook.refreshall
11. On Error GoTo 0
12. '2969 Argentia
13. Workbooks("book23.xlsx").Activate
14. On Error Resume Next
15. ActiveWorkbook.Sheets("WDed12").Activate
16. lastrow = ActiveWorkbook.ActiveSheet.Cells(rows.count, 1).End(xlUp).Row
17. lastcol = ActiveWorkbook.ActiveSheet.Cells(3, Columns.count).End(xlToLeft).Column
18. ActiveSheet.Range(Cells(2, 1), Cells(lastrow, lastcol)).Copy

thanks so much in advance

 

[louisH]

Hi,

Sub yoursub()

Workbooks("book23.xlsx").Activate
On Error Resume Next
If SheetExists("WDed15") = False Then Goto Skip
ActiveWorkbook.Sheets("WDed15").Activate
lastrow = ActiveWorkbook.ActiveSheet.Cells(rows.count, 1).End(xlUp).Row
lastcol = ActiveWorkbook.ActiveSheet.Cells(3, Columns.count).End(xlToLeft).Column
ActiveSheet.Range(Cells(2, 1), Cells(lastrow, lastcol)).Copy
Workbooks("Billing Report 30 Titan.xlsx").Activate
lastrow1 = ActiveWorkbook.Worksheets(Worksheets.count).Cells(rows.count, 1).End(xlUp).Row
ActiveWorkbook.Worksheets(Worksheets.count).Range("A" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
 ActiveWorkbook.refreshall
 On Error GoTo 0
 '2969 Argentia
Skip :
 Workbooks("book23.xlsx").Activate
 On Error Resume Next
 ActiveWorkbook.Sheets("WDed12").Activate
 lastrow = ActiveWorkbook.ActiveSheet.Cells(rows.count, 1).End(xlUp).Row
 lastcol = ActiveWorkbook.ActiveSheet.Cells(3, Columns.count).End(xlToLeft).Column
 ActiveSheet.Range(Cells(2, 1), Cells(lastrow, lastcol)).Copy

End Sub


Function SheetExists(name As String) As Boolean
    SheetExists = False
    
    For Each ws In ThisWorkbook.Worksheets
        
        If ws.name = name Then
            SheetExists = True
            Exit Function
        End If
    
    Next ws
End Function

 

[lynnsong986]

Thank you so much LouisH, works great.
Lynn