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 :)
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 :)