Creating sheet in another workbook if not exists

[jdr360]

I'm having issues with the following code:

'================================
' Create Worksheet if not exists
'================================
Function CreateSheetIf(strSheetName As String) As Boolean
    
    Dim blnRetVal As Boolean
    blnRetVal = False
    Dim wks As Worksheet
    Dim wbkTarget As Workbook
    If strSheetName = "Cancelled" Then
        Set wbkTarget = Application.Workbooks("Cancelled.xlsm")
    Else
        Set wbkTarget = Application.Workbooks("Finance.xlsm")
    End If
    For Each wks In wbkTarget.Worksheets
        If StrComp(wks.Name, strSheetName) = 0 Then
            blnRetVal = True
            Exit For
        Else
            ThisWorkbook.Sheets("Template").Visible = True
            ThisWorkbook.Sheets("Template").Copy after:=wbkTarget.Sheets(Sheets.Count)
            ActiveSheet.Name = strSheetName
            ThisWorkbook.Sheets("Template").Visible = False
        End If
    Next wks
    CreateSheetIf = blnRetVal


End Function

It does create the worksheets, however, it looks like it's trying to create two copies of the sheets. In the Finance workbook, I need it to rename the template worksheet after the template has been copied. This works correctly, but when I check my Finance workbook, I have sheet names:

04-Feb-2018 - 17-Feb-2018 <<== Created since it did not exist
Template (2) <<== Looks like it's trying to create it again
18-Feb-2018 - 03-Mar-2018 <<== Created since it did not exist
Template (3) <<== Again, looks like its trying to create it again

The command I pass to this function is:

CreateSheetIf (pSheet)  ' Create Previous finance period
CreateSheetIf (cSheet)  ' Create Current finance period

The pSheet and cSheet is defined with Dates, I'm not worried about the Cancelled workbook and sheet, as this looks like it's functioning normally, I do not see any Template(#) sheets. Just having issues with the Finance workbook and it creating Template(#) sheets. I'm not sure where the code is doing the duplicate Template copy.

 

[MartyS]

Is there any error handling in the code calling the CreateSheetIf function (or in any of the code calling the code that calls the CreateSheetIf function, etc)


e.g.

On error resume next

some code

result = CreateSheetIf(psheet)
result = CreateSheetIf(csheet)


some code

on error goto 0

 

[jdr360]

There is no error handling before the call to the function. There is after the call, but that is for something else. Is that what's missing? Just error handling?

 

[MartyS]

Let's say there are currently 3 worksheets in the FINANCE workbook.

What the code currently does is:


For EACH worksheet in the FINANCE workbook:

(if it's name is not the same as the name you're passing in)

Unhide the TEMPLATE worksheet in this workbook
Copy the TEMPLATE worksheet in this workbook
Copy it to after the number of sheets in this workbook (and not after the number of worksheets in the FINANCE workbook!!)

Rename the activesheet (we're now in the FINANCE workbook, renaming the new TEMPLATE worksheet).


Repeat (for each worksheet already in the FINANCE workbook!!)



So.. let's say you call the routine once, and pass the string "hello" and there are three worksheets (a, b and c) in the FINANCE workbook...


The first time it will unhide and then copy the TEMPLATE worksheet from (I'm assuming the routine is called from the CANCELLED workbook)..
It will place it into the FINANCE workbook.
It will rename it to "hello"

That's for sheet "a" in the FINANCE workbook (i.e. you said "for each worksheet I find in the FINANCE workbook, do the following")


Now.. because there are two other sheets in the FINANCE workbook... (b and c)

do it again! Unhide and copy the TEMPLATE worksheet...
Copy it to the FINANCE workbook
rename it to "hello" - but can't do that because that now exists... so we now have a TEMPLATE worksheet...


now do it the third time (for sheet c)

Unhide, copy... can't call it template as that exists.. so call it TEMPLATE (2)

can't rename template (2) to hello because that already exists...

Now we have:


FINANCE:

a, b, c, hello, template, template(2)




Hope that makes sense?



Out of curiosity, could you (temporarily) add immediately after the function name:

[COLOR=#333333]Function CreateSheetIf(strSheetName As String) As Boolean[/COLOR]

On error goto 0


so it looks like this

[COLOR=#333333]Function CreateSheetIf(strSheetName As String) As Boolean[/COLOR]

On error goto 0

 

[jdr360]

I have tried the following modification:

'================================
' Create Worksheet if not exists
'================================
Function CreateSheetIf(strSheetName As String) As Boolean
    
    On Error GoTo 0
    Dim blnRetVal As Boolean
    blnRetVal = False           ' Assume sheet does not exists
    Dim wks As Worksheet
    Dim wbkTarget As Workbook
    If strSheetName = "Cancelled" Then                          ' If sheet name is Cancelled
        Set wbkTarget = Application.Workbooks("Cancelled.xlsm") ' Use Cancelled workbook as target workbook
    Else                                                        ' otherwise
        Set wbkTarget = Application.Workbooks("Finance.xlsm")   ' Use Finance workbook as target workbook
    End If
    ThisWorkbook.Sheets("Template").Visible = True                                      ' Unhide Template in ThisWorkbook
    For Each wks In wbkTarget.Worksheets                                                ' Search all worksheets in target workbook
        If StrComp(wks.Name, strSheetName) = 0 Then                                     ' If sheet exists   ((This is where I think the issue is))
            blnRetVal = True                                                            ' Return true       ((it's not seeing that the sheet    ))
            Exit For                                                                    ' then exit         ((already exists                    ))
        Else                                                                            ' otherwise
            ThisWorkbook.Sheets("Template").Copy after:=wbkTarget.Sheets(Sheets.Count)  ' copy Template to end of target workbook
            ActiveSheet.Name = strSheetName                                             ' rename the sheet to finance period being worked on
        End If
    Next wks                                                                            ' continue searching target workbook
    ThisWorkbook.Sheets("Template").Visible = False                                     ' hide Template sheet in ThisWorkbook
    CreateSheetIf = blnRetVal


End Function

I put the On Error Goto 0 at the top, it's still adding the extra sheets (Template (#)), even though the two worksheets already exists. I will try to work on this tomorrow, my work day is done. Thank you for the help with this. I put comments in the code to help understand what it's doing (more for me than anyone else, as I'm not an expert with VBA).

 

[MartyS]

Perhaps this explanation will be easier:

There are TWO key errors in your code.


1. You're not copying to the end of the Target workbook.

2. You have included the "COPY.... COPY TO... RENAME..." functionality within the "does this worksheet name already exist in the target workbook"
Shouldn't it be outside of that search?

e.g.

    For Each wks In wbkTarget.Worksheets                                ' Go through each worksheet in the Target workbook
        If StrComp(UCase(wks.Name), UCase(strSheetName)) = 0 Then       ' If sheet exists
            blnRetVal = True                                            ' Return true
            Exit Function                                               ' then exit the FUNCTION
        End If
    Next wks




    'Now we know the sheet name we want to create does not already exist


    ThisWorkbook.Sheets("Template").Visible = True
    ThisWorkbook.Sheets("Template").Copy after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)    ' copy Template to end of target workbook
    ActiveSheet.Name = strSheetName                                                         ' rename the sheet to finance period being worked on
    ThisWorkbook.Sheets("Template").Visible = False                                         ' Hide the original TEMPLATE sheet


    CreateSheetIf = blnRetVal                                                               ' Sheet did NOT exist, return FALSE as it was created


End Function

 

[jdr360]

Perhaps this explanation will be easier:

There are TWO key errors in your code.


1. You're not copying to the end of the Target workbook.

2. You have included the "COPY.... COPY TO... RENAME..." functionality within the "does this worksheet name already exist in the target workbook"
Shouldn't it be outside of that search?

e.g.

    For Each wks In wbkTarget.Worksheets                                ' Go through each worksheet in the Target workbook
        If StrComp(UCase(wks.Name), UCase(strSheetName)) = 0 Then       ' If sheet exists
            blnRetVal = True                                            ' Return true
            Exit Function                                               ' then exit the FUNCTION
        End If
    Next wks




    'Now we know the sheet name we want to create does not already exist


    ThisWorkbook.Sheets("Template").Visible = True
    ThisWorkbook.Sheets("Template").Copy after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)    ' copy Template to end of target workbook
    ActiveSheet.Name = strSheetName                                                         ' rename the sheet to finance period being worked on
    ThisWorkbook.Sheets("Template").Visible = False                                         ' Hide the original TEMPLATE sheet


    CreateSheetIf = blnRetVal                                                               ' Sheet did NOT exist, return FALSE as it was created


End Function

Thank you, I just got back to work and tried your suggestion. It worked like a charm! Very happy. I`m surprised I missed that. Like I said, I`m no expert, but I do some programming. Guess I just needed a different point of view. Thank you for your help. I may have other questions. I have been kinda picked as the go to guy to create a rather large project for maintaining a database in excel. I have the foundation complete. Now just trying to get all the bugs out.

 

[MartyS]

You're welcome! Glad it helped.