VBA Driving me nuts

[Mbradb]

I've been working on a code for some time now. I think I am over complicating the coding for this macro. Essentially what I want it to be able to do is copy a date from a variable range cell that contains a selectable date and copy it as the worksheet name. I want the macro to error out and supply a message if a worksheet with that name already exists. I am going to assign the macro to a button.

In fool proofing this workbook... I feel as though I have become the fool.

This is why I have asked for a 936pg VBA programming book for Christmas...

Here is the coding that I have so far:

Sub Submit_For_Archiving()


' Submit_For_Archiving Macro
' Macro recorded 12/8/2008 by MBRADB3


Dim ws As Worksheet, sExist As Boolean
For Each ws In Worksheets
If UCase(ws.Range("D4")) = UCase(ws.Name) Then
sExist = True
Exit For
End If
Next
If sExist = True Then
MsgBox "This date already exists. Please make sure the date selected reflects the correct weeks production"
Else

ActiveSheet.Unprotect
Sheets("Production Board").Select
Sheets("Production Board").copy Before:=Sheets(1)
Sheets("Production Board (2)").Select

On Error Resume Next
For Each ws In Worksheets
If (ws.Range("D4")) = "" Then
ws.Name = "Sheet" & ws.Index
Else
ws.Name = Format(ws.Range("D4"), "mm-dd-yyyy")
End If

Next

ActiveSheet.Shapes("Text Box 1").Select
Selection.delete
ActiveSheet.Shapes("Text Box 2").Select
Selection.delete
ActiveSheet.Shapes("Text Box 3").Select
Selection.delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Production Board").Select
Range("C6:I8,C10:I12,C14:I16,C21:I23").Select
Range("I21").Activate
Selection.ClearContents
Range("C6").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Production Board").Select

End If
End Sub

 

[PHV]

And the problem is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mbradb]

The coding creates the correctly named worksheet based on mm-dd-yyyy so long as it doesn't already exist. However, it doesn't recognize if a worksheet with the name it's trying to create exists. So, it doesn't give the error message and it creates a second copy of the original worksheet i.e. "production board (2)" instead.

 

[PHV]

Replace this:
If UCase(ws.Range("D4")) = UCase(ws.Name) Then
with this?
If ws.Name = Format(ws.Range("D4"), "mm-dd-yyyy") Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mbradb]

Okay, I've made the suggested change in coding. I'm getting the error message, which is great.

Unfortunately, that is all I am getting, regardless of a worksheet with that name existing or not. So if I already have 1-5-2009 it gives me the error. If I am trying to create another worksheet where the name does not exist it will still give me the error and will not move forward on the code.

 

[SkipVought]

Did you employ my tip I gave you in your FIRST thread regarding this issue?

If so, what did you observe?

If not, why not?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What error message on what statement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}