Defining sheets to be created

[syrney170]

I have run into an issue where for larger projects my company now wants
to have multiple planned sheets for these larger projects. maybe the
breakdown will correspond to the number of deliverables. That is not
important to know for this question. I am trying to create a single
macro that can be used to see if a sheet called "Resource Plans"
exists. The once it is verified then create an additional sheet called
"Resource Plans-1" .On each new Resource Plan sheet I want it to copy
the format of either the original "Resource Plans" sheet or of the
previously created Resource Plans sheet. Also, I want to it to only
add a planned sheet each time you run the macro but each time to go up
one count numerically within the name of the sheet (i.e. Resource
Plans-1, Resource Plans-2, etc.) I am still fairly new to macros and
have been able to figure out quite a bit, but I am stil learning alot
too. I have tried doing this operation numerous ways. I am not sure
if the best structure is if statements, case arguments, or who knows. I
figure that for the macro to be run once and create a sheet named
"Resource Plans-1" and then to run the macro again when you want an
additional sheet called "Resource Plans-2" will involve many if
statements. I also know that the second time through it would have to
check to see if "Resource Plans" and "Resource Plans-1" sheets exist to
know to add the next sheet. I hope their is an efficient way to do all
of this. What I have been trying to do is ugly. I appreciate any help
I can get.

Sincerely,
Kyle

Here is my current code in case it helps. I will say that I know it is
horribly flawed.

Sub NewResPlanSh()
'
' NewResPlanSh Macro
' Macro recorded 1/10/2006 by jkjordan
'
'
'
' For Each ws In Worksheets
' MsgBox ws.Name
'Next ws
'
'Set NewSheet = Worksheets.Add
'NewSheet.Name = "current Budget"





Dim WS As Worksheet

For Each WS In Workbooks(1).Worksheets
Select Case WS.Name
Case Is = "Resource Plans"
Worksheets("Resource Plans").Activate
Range("A1:M26").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("J24").Select
Columns("J:J").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 23.57
Columns("B:B").ColumnWidth = 17.86
Columns("J:J").ColumnWidth = 12
Range("B12:H20").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C5:J9").Select
Selection.ClearContents
Columns("I:I").ColumnWidth = 12
Columns("B:B").ColumnWidth = 21.57
Range("D16").Select
ActiveSheet.Name = "Resource Plans-1"
Case Is = "Resource Plans-1"
Worksheets("Resource Plans-1").Activate
Range("A1:M26").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("J24").Select
Columns("J:J").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 23.57
Columns("B:B").ColumnWidth = 17.86
Columns("J:J").ColumnWidth = 12
Range("B12:H20").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C5:J9").Select
Selection.ClearContents
Columns("I:I").ColumnWidth = 12
Columns("B:B").ColumnWidth = 21.57
Range("D16").Select
ActiveSheet.Name = "Resource Plans-2"
Case Is = "Resource Plans-2"
Worksheets("Resource Plans-2").Activate
Range("A1:M26").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("J24").Select
Columns("J:J").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 23.57
Columns("B:B").ColumnWidth = 17.86
Columns("J:J").ColumnWidth = 12
Range("B12:H20").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C5:J9").Select
Selection.ClearContents
Columns("I:I").ColumnWidth = 12
Columns("B:B").ColumnWidth = 21.57
Range("D16").Select
ActiveSheet.Name = "Resource Plans-3"
End Select
Next WS


End Sub

 

[syrney170]

I have noticed that all of the topics around mine got people to post
replies and to offer help with their problem. It seems that mine kind
of got skipped over. Is there anything I can do to help clarify what I
was asking for help on that would make it easier to give me advice on
my problem? I really need help on this guys.

-Kyle-