vbaexcelex

Opening worksheets based on Cell names in VBA


it's my first time posting on Stack Overflow. I am trying to use VBA to get it to create a new worksheet based on a cell value in sheet 1. But if the sheet already exists I need it to open that sheet instead. I'm having difficulty with this as I don't actually know the name of the sheet. I thought I could do this if I create another sheet where it stores the names of projects, using a counter. It shows me I have run-time error 91. This is the code I currently have:

Public Sub DailyReport()

Dim project As Range
project = Worksheets("Target Flow").Range("B3")

Dim i As Integer
i = 1

If Worksheets("Target Flow").Range("B3") <> 
Worksheets("Projects").Cells(1000, 1).Value Then

Worksheets("Target Flow").Range("B3").Select
Selection.Copy

Worksheets("Projects").Activate
Cells(i, 1).Select
ActiveSheet.Paste


Dim WS As Worksheet
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))

WS.Name = project.Value

i = i + 1

Else

Worksheets("Target Flow").Activate
Worksheets(ActiveSheet.Range("B3").Value).Activate

End If

End Sub

If anyone could guide me in the right direction, I'd be grateful!!


Solution

  • This code will scan all sheets in the active workbook to see if there is a name match, if there is it will activate it. After the loop if it doesn't see a match was made it will create it.

    Dim targetSheetName As String
    Dim targetSheetFound As Boolean
    Dim sheet As Worksheet
    
    targetSheetName = Worksheets("Target Flow").Range("B3")
    targetSheetFound = False
    
    For Each sheet In ActiveWorkbook.Worksheets
    
        If sheet.Name = targetSheetName Then
    
            targetSheetFound = True
            sheet.Activate
    
        End If
    
    Next
    
    If Not targetSheetFound Then
    
        set sheet = Sheets.Add
        sheet.Name = targetSheetName
    
    End If