Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining sheets to be created
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining sheets to be created
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Name Defining | Excel Discussion (Misc queries) | |||
Defining | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
defining unique range of cells for different sheets as the same n. | Excel Discussion (Misc queries) | |||
Defining a variable within a sub... | Excel Programming |