PDA

View Full Version : Create sheet if it doesn't exist problem



Jomathr
07-25-2013, 06:06 PM
Good evening everyone,

I have a piece of code that check if the sheet exist or not and create it if it doesn't:


For Each cAcc In Range(LastAccount).Cells If Mid(cAcc.Value, 10, 3) = "112" Then
SheetName = "Conc_" & cAcc.Value
On Error Resume Next
Set wsAcc = Sheets(SheetName)
If Not wsAcc Is Nothing Then
Worksheets("Conc_" & cAcc.Value).Visible = True
reclcick = True
Else
Sheets("Template_Conc_Bank").Visible = True
Sheets("Template_Conc_Bank").Copy After:=ActiveWorkbook.Sheets("Template_Conc_Bank")
Sheets("Template_Conc_Bank (2)").Select
Sheets("Template_Conc_Bank (2)").Name = "Conc_" & cAcc.Value
Range("ConcAccountNo") = cAcc.Value
Range("ConcAccountName") = cAcc.Offset(0, -1).Value
Sheets("Template_Conc_Bank").Visible = False
Set wsAcc = Nothing
End If
End If
Next cAcc

If none of the sheets are created it works really well and create all the sheets corresponding to the condition. But if I add one account to the list that respect the condition "112" then it doesn't create the sheet. the problem seem to be happening in that bit:


Set wsAcc = Sheets(SheetName)
If Not wsAcc Is Nothing Then

Even thought I reset the wsAcc it doesn't seem like it reset correctly and detect the new account as thought it already exist even if it doesn't.

Anyone have any clue why? :banghead:

Thank you in advance for the help :)

Paul_Hossler
07-25-2013, 07:57 PM
If Mid(cAcc.Value, 10, 3) = "112" Then
SheetName = "Conc_" & cAcc.Value


What is the value in cAcc?

I see positions 10, 11, and 12 = "112", but depending on the rest of the cell, SheetName might be invalid for some reason

Since you said to ignore errors ...



On Error Resume Next
Set wsAcc = Sheets(SheetName)


... it could fail later on


Paul

Kenneth Hobs
07-25-2013, 08:17 PM
I would use this.

Function Test_WorkSheetExists()
MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), vbInformation, "ActiveWorkbook.ActiveSheet"
End Function

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function

snb
07-26-2013, 01:26 AM
or

For Each cAcc In Range(LastAccount).Cells
If Mid(cAcc.Value, 10, 3) = "112" and not evaluate("isref(" & "Conc_" & cAcc.Value & "!A1)") then
with Sheets("Template_Conc_Bank")
.visible=true
.Copy ,Sheets("Template_Conc_Bank")
.visible=false
end with
Sheets(sheets.count).Name = "Conc_" & cAcc.Value
end if
Next

Jomathr
07-26-2013, 06:07 AM
Thank you all for your replies,

I used Kenneth's approach to use a function to return if sheet is working or not and it work like a charm now.


For Each cAcc In Range(LastAccount).Cells If Mid(cAcc.Value, 10, 3) = "112" Then
SheetName = "Conc_" & cAcc.Value
' On Error Resume Next
' Set wsAcc = Sheets(SheetName)
If WorkSheetExists("Conc_" & cAcc.Value) = True Then
Worksheets("Conc_" & cAcc.Value).Visible = True
reclcick = True
Else
Sheets("Template_Conc_Bank").Visible = True
Sheets("Template_Conc_Bank").Copy After:=ActiveWorkbook.Sheets("Template_Conc_Bank")
Sheets("Template_Conc_Bank (2)").Select
Sheets("Template_Conc_Bank (2)").Name = "Conc_" & cAcc.Value
Range("ConcAccountNo") = cAcc.Value
Range("ConcAccountName") = cAcc.Offset(0, -1).Value
Sheets("Template_Conc_Bank").Visible = False
Set wsAcc = Nothing
End If
End If
Next cAcc

As for Paul, cAcc in this range is just a variable to loop in the list Dim cAcc as range and the way I was using my loop/condition the error part was necessary in case the name doesn't exist.
I"m still not sure as to why it failed since I went trough all the list step by step to check (F8) and the condition all seemed to be handled correctly.

snb: I didn't use your code because the emplacement on the new sheet name is almost never equal to the number of sheets in the workbook since a lot of operation deal with creating/deleting ws.

Thank you again :)

snb
07-26-2013, 06:17 AM
snb: I didn't use your code because the emplacement on the new sheet name is almost never equal to the number of sheets in the workbook since a lot of operation deal with creating/deleting ws.


After copying a sheet, the new sheet has always the indexnumber equal to the number of sheets in the workbook !

Jomathr
07-26-2013, 06:25 AM
right, I wasn't thinking straight... not done with my first cofee of the day yet, thought it was the sheet name number at first glance

snb
07-26-2013, 08:18 AM
That's the European advantage ;)