How to see newly created sheets with GetSheet(j)?

[#1]

Hello,

I have a macro which should dynamicaly create sheets if they are not already created. I do this by looping through the sheets and comparing their name.

Unfortunately newly created sheets are not seen from the macro.

See the code for reference:

For i = 0 To unimportant.Count-1
     'Define the name of the sheet to check
     Select Case i
     Case 0
          sheetName = "one"
     Case 1
          sheetName = "two"
     Case 2
          'It's the same name as in Case 0, so there shouldn't be any sheet created.
          sheetName = "one"
     Case Else
          sheetName = "three"
     End Select
     createNew = true
     For j = 0 To ActiveDocument.NoOfSheets-1
          If (StrComp(ActiveDocument.GetSheet(j).GetProperties.Name, sheetName) = 0) Then
               'This fires if there was a sheet with sheetName before starting the macro, but not if the sheet was created in the if statement later.
               '(In this version it should fire when i = 2, but it doesn't, so a second sheet with name "one" is created.)
               createNew = false
          End If
     Next
     'This works fine and the newly created sheet is also shown correctly, but is not seen in the for-Statement above.
     If createNew Then
          Set newSheet = ActiveDocument.CreateSheet()
          Set newProperties = newSheet.GetProperties
          newProperties.Name = sheetName
          newSheet.SetProperties(newProperties)
          'I think something should go here so that the macro loads the new sheets into memory or whatever.
     End If
Next

I think there should be a function to update the sheets for the macro, so it sees all of the sheets again and not only the ones that were there before starting the macro, but I can't find it.

Alternatively, do you see any other possibility to do this?

[Anonymous]

For i = 0 To unimportant.Count-1
     'Define the name of the sheet to check
     Select Case i
     Case 0
          sheetName = "one"
     Case 1
          sheetName = "two"
     Case 2
          'It's the same name as in Case 0, so there shouldn't be any sheet created.
          sheetName = "one"
     Case Else
          sheetName = "three"
     End Select

     createNew = true

     For j = 0 To ActiveDocument.NoOfSheets-1
          If (StrComp(ActiveDocument.GetSheet(j).GetProperties.Name, sheetName) = 0) Then
               'This fires if there was a sheet with sheetName before starting the macro, but not if the sheet was created in the if statement later.
               '(In this version it should fire when i = 2, but it doesn't, so a second sheet with name "one" is created.)
               createNew = false
          End If
     Next

     'This works fine and the newly created sheet is also shown correctly, but is not seen in the for-Statement above.
     If createNew Then
          Set newSheet = ActiveDocument.CreateSheet()
          Set newProperties = newSheet.GetProperties
          newProperties.Name = "sheetName"
          newSheet.SetProperties(newProperties)
          'I think something should go here so that the macro loads the new sheets into memory or whatever.
     End If
Next

newProperties.Name = "sheetName"

add Double quotes to add sheet name ...search sheet using name

[#3 · Accepted solution]

sheetName is actualy a String variable, but I could try searching the sheet by the name instead of comparing Strings, you are right.

EDIT: I did it a bit more different than intended before, but here is my solution:

Set workSheet = Nothing                                           'You have to do this, otherwise workSheet will keep the previously found sheet,
                                                                               'if there is no sheet found in the next statement.
Set workSheet = ActiveDocument.GetSheet(sheetName)
On Error Resume Next                                              'If you don't add this, the macro will raise an error with no message in the previous line.
If (TypeName(workSheet) = "Nothing") Then                'Here's how you check, if GetSheet returned a sheet.
    Set newSheet = ActiveDocument.CreateSheet()
    Set newProperties = newSheet.GetProperties
    newProperties.Name = sheetName
    newSheet.SetProperties(newProperties)
End If

The funny thing is, if I print out the name of the newly created sheet, it will appear as the standard name it was created with ("Arbeitsblatt #" in German QV), but it is still found, if you put the name you have given it in GetSheet (as it is done in later iterations of the loop for example or later in the code for adding SheetObjects to it).