Problems with go to label function

Question

I am running the following macro to open 130 excel files, and copy them in separate sheets for each entity. It sort of works for what I want to do (although probably not the most efficient). Once a sheet already exists, I want the macro not to create a new sheet again, but skip this step.

I am trying to use the on error code for this, however I can't get the following part to work properly:

On Error Goto skip

Even if the formula below this code has an error, it keeps giving back the notification "subscript out of range".

Does anyone have a clue what I'm doing wrong here?

Sub Macro2()

'Select item Location
Row = 2
nextitem: Row = Row + 1

' Create sheet
Sheets("Location").Select
Week = ActiveSheet.Range("c1").Value
Complete = ActiveSheet.Range("b" & Row).Value
Entity = ActiveSheet.Range("a" & Row).Value
Workbook_Entity = Entity & " - Week " & Week & ".xlsx"

If Complete = "Yes" Then
    GoTo nextitem
    Else

On Error GoTo Skip
Sheets(Entity).Select
GoTo Skipped2

Exit Sub

Skip:
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Entity

Skipped2:

'Open Workbooks

Workbooks.Open Filename:=ThisWorkbook.Path & "\location\" & Workbook_Entity

Sheets("Week - Hidden").Visible = True
Sheets("Week - Hidden").Select
Columns("A:G").Select
Selection.Copy
Windows("Overview.xlsm").Activate
Sheets(Entity).Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows(Workbook_Entity).Activate
ActiveWorkbook.Save
Sheets("Week - Hidden").Visible = False
ActiveWindow.Close

'Rotate
If Item_Region = "003" Then

GoTo Enditall

Else

GoTo nextitem

Enditall:

End If
End If

End Sub

Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba, don't use Goto and use a function or sub to check whether a sheet exists rather than OE. — SJR, Sep 29 '20 at 11:27
Does this answer your question? [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — Geert Bellekens, Sep 29 '20 at 13:16

score 0 · Answer 1 · answered Sep 29 '20 at 12:35

Close but better still would be

Function TryGetSheet(ByVal ipSheetName As String, ByRef opSheet as worksheet) as Boolean

   On Error Resume Next
   Set opSheet = ThisWorkbook.sheets(ipSheetName) 
   TryGetSheet = Err.Number = 0
   On Error GoTo 0

End Function

score -2 · Answer 2 · answered Sep 29 '20 at 12:23

-2

I found the answer, which solved it for me. hope it might help someone else as well

If DoesSheetExists(s) Then
GoTo Skipped2

Else
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Entity
Skipped2:

End If

with the following function

Function DoesSheetExists(sh As String) As Boolean
Dim ws As Worksheet

On Error Resume Next
Set ws = ThisWorkbook.Sheets(sh)
On Error GoTo 0

If Not ws Is Nothing Then DoesSheetExists = True
End Function

answered Sep 29 '20 at 12:23

ErikSlui

77
3
10

1

Bad idea, stay away from using `GoTo` (only use it for error handling)! `GoTo` is a very bad practice and you will easily run into issues soon or later. Also your code will get unmaintainable very quickly because it turns into one big spaghetti monster. Instead use something like `If Not DoesSheetExists(s) Then … Sheets.Add… End If` no `Else` needed and no jumping with `GoTo` and no skipping. – Pᴇʜ Sep 29 '20 at 13:13

Problems with go to label function

2 Answers2