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!!
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