Automate creating a worksheet adding rows based on certain criterias (all this in the same workbook)

[pratikjoshi448]

I'm relatively new to automating the macros. It's easy to directly automate static steps but to make it more dynamic I'm having trouble writing the script. Here's my criteria.

I prepared a workbook to keep track of the tasks in a project. Here's my criteria.

1. I created an estimation summary tab with "Button 1" having macro to create a new worksheet based on "Sprint Plan" tab.
2. I tried renaming the tab to "Detail Estimation" but I had to delete or rename every time so for now I'm creating a "Sprint Plan (copy number)" tabs. I'm copy pasting the contents from "Sprint Plan (2)" tab in Detail Estimation for now.
3. The "Button 1" creates the SprintPlan (2)" tab. I was able to add rows but it is a static approach. I want to create rows in my new sheet based on the criteria that the 1st, 2nd, and 3rd column contents are not null. This has to be iterative and should be applied to very last row content.
4. I am trying to create a worksheet that matches the content of "DetailEstimation".

The original sheet and please ignore the formatting:

Template Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Tasks	Tickets	Tracker	Category	Priority	BA	Dev	QA	Status	Schedule Status	Complex-ities	Week	Target Version	Start Date	Task Deadline	QC Date	UAT/ Review Date	UAT/ Review By	Risks/Remarks	Deermine
2	Sprint Tasks
3	Development Tasks
4	Module Documentations for Encounters	123454																		123454
5	Test row 1	123455	Feature		Normal	Member 1														123455
6	test row 2	123456	Feature		Normal	Member 1		Member 7												123456
7	Interest Payment	123457	Feature		High	Member 2		Member 8	Not Started	On Track										123457
8	Third Party Billing	123458	Feature		Normal	Member 3		Member 9	Completed	On Track										123458
9	Technical Design Documentation
10	>>TDD - for test status	123465	Technical Design		Normal		Member 4		Not Started	On Track										123465
11	>>TDD - for some random task	123466	Technical Design		Normal		Member 5	Member 7	Not Started	On Track										123466
12	>>TDD - Inquiry screen	123467	Technical Design		Normal		Member 6	Member 8	Not Started	On Track										123467
13	>>TDD - Upload file	123468	Technical Design		Normal		Member 5	Member 9	Not Started	On Track										123468
SprintPlan

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S9	Cell Value	contains "On Track"	text	NO
A9,U9:AA9	Cell Value	contains "On Track"	text	NO
S3:T3	Cell Value	contains "On Track"	text	NO
A3,U3:AA3	Cell Value	contains "On Track"	text	NO
P12:P13	Cell Value	="Coding"	text	NO
P12:P13	Cell Value	="Dev Completed"	text	NO
P12:P13	Cell Value	="Released"	text	NO
P12:P13	Cell Value	contains "On Track"	text	NO
P10:P11	Cell Value	="Coding"	text	NO
P10:P11	Cell Value	="Dev Completed"	text	NO
P10:P11	Cell Value	="Released"	text	NO
P10:P11	Cell Value	contains "On Track"	text	NO
P7:P8	Cell Value	="Coding"	text	NO
P7:P8	Cell Value	="Dev Completed"	text	NO
P7:P8	Cell Value	="Released"	text	NO
P7:P8	Cell Value	contains "On Track"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I7:I8	List	=Phase!$A$2:$A$19
J7:J8	List	=Phase!$F$2:$F$9
I10:I13	List	=Phase!$A$2:$A$19
J10:J334	List	=Phase!$F$2:$F$9

The output I'm trying to target:

Template Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Tasks	Tickets	Tracker	Priority	Team	Resource	Week	Target Version	Total Person Hrs	Documentation	Spec	Research	TDD	Coding	Dev Testing	Code Review/QAT	Test Scenario	Test Cases	Testing	Rework	Risks/Remarks	Deermine
2	Sprint Tasks
3	Development Tasks
4	Module Documentations for Encounters	123454							0											0		123454
5	Test row 1	123455	Feature						0											0		123455
6					BA	Member 1
7					Dev
8					QA
9	test row 2	123456	Feature						0											0		123456
10					BA	Member 1
11					Dev
12					QA	Member 7
13	Interest Payment	123457	Feature	High					0											0		123457
14					BA	Member 2
15					Dev
16					QA	Member 8
17	Third Party Billing	123458	Feature	Normal					0											0		123458
18					BA	Member 3
19					Dev
20					QA	Member 9
21	Technical Design Documentation								0											0
22	>>TDD - for test status	123465	Technical Design	Normal					0											0		123465
23					BA
24					Dev	Member 4
25					QA
26	>>TDD - for some random task	123466	Technical Design	Normal					0											0		123466
27					BA
28					Dev	Member 5
29					QA	Member 7
30	>>TDD - Inquiry screen	123467	Technical Design	Normal					0											0		123467
31					BA
32					Dev	Member 6
33					QA	Member 8
34	>>TDD - Upload file	123468	Technical Design	Normal					0											0		123468
35					BA				0											0
36					Dev	Member 5			0											0
37					QA	Member 9			0											0
SprintPlan (2)

Cell Formulas
Range	Formula
I4:I5,I34:I37,I30,I26,I21:I22,I17,I13,I9	I4	=SUM(J4:T4)
T4:T5,T34:T37,T30,T26,T21:T22,T17,T13,T9	T4	=0.3*SUM(J4:S4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U21	Cell Value	contains "On Track"	text	NO
A21,W21:AC21	Cell Value	contains "On Track"	text	NO
U3:V3	Cell Value	contains "On Track"	text	NO
A3,W3:AC3	Cell Value	contains "On Track"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E1:E3	Any value

Here's my macro:

Sub PJ_Macro1()
'
' PJ_Macro1 Macro
'

' Create a duplicate sheet and select before the "Estimation Summary" tab or Sheet4.
    Sheets("SprintPlan").Select
    Sheets("SprintPlan").Copy Before:=Sheets(4)
    
' Delete unwanted columns from "Sprint Plan" tab
' and add the columns used to allocate estimated hours i.e. from "Total person hrs" to "Rework"
' Minor formatting changes done like adjust column width
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.FormatConditions.Delete
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Team"
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.FormatConditions.Delete
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Total Person Hrs"
    Range("I2").Select
    Columns("I:I").ColumnWidth = 9.57
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    ' Adding columns for the estimated hours
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Documentation"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Spec"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Research"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "TDD"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Coding"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Dev Testing"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Code Review/QAT"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Test Scenario"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Test Cases"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Testing"
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Rework"
    Columns("J:T").Select
    Columns("J:T").EntireColumn.AutoFit
    Range("I4").Select
    Application.CutCopyMode = False
    ' Sum the total hours for total person hours.
    ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[11])"
    Range("I4").Select
    Selection.AutoFill Destination:=Range("I4:I28"), Type:=xlFillDefault
    Range("I4:I28").Select
    Range("T4").Select
    Application.CutCopyMode = False
    ' This is to calculate the 30% of the total hour for that row
    ActiveCell.FormulaR1C1 = "=0.3*SUM(RC[-10]:RC[-1])"
    Range("T4").Select
    Selection.AutoFill Destination:=Range("T4:T28"), Type:=xlFillDefault
    Range("T4:T28").Select
    Columns("U:U").ColumnWidth = 33.71
    

' Insert rows without the loop
    Rows("8:8").Select
    ' Shift and copy formatting from left or above.
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ' Add the team details
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "BA"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "Dev"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "QA"
    
    
End Sub

 

[pratikjoshi448]

Any suggestions here would be really helpful.