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

pratikjoshi448

New Member
Joined
Jul 22, 2021
Messages
6
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
1TasksTicketsTrackerCategoryPriorityBADevQAStatusSchedule StatusComplex-itiesWeekTarget VersionStart DateTask DeadlineQC DateUAT/ Review DateUAT/ Review ByRisks/RemarksDeermine
2Sprint Tasks
3Development Tasks
4Module Documentations for Encounters123454123454
5Test row 1123455FeatureNormalMember 1123455
6test row 2123456FeatureNormalMember 1Member 7123456
7Interest Payment123457FeatureHighMember 2Member 8Not StartedOn Track123457
8Third Party Billing123458FeatureNormalMember 3Member 9CompletedOn Track123458
9Technical Design Documentation
10>>TDD - for test status123465Technical DesignNormalMember 4Not StartedOn Track123465
11>>TDD - for some random task123466Technical DesignNormalMember 5Member 7Not StartedOn Track123466
12>>TDD - Inquiry screen123467Technical DesignNormalMember 6Member 8Not StartedOn Track123467
13>>TDD - Upload file123468Technical DesignNormalMember 5Member 9Not StartedOn Track123468
SprintPlan
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S9Cell Valuecontains "On Track"textNO
A9,U9:AA9Cell Valuecontains "On Track"textNO
S3:T3Cell Valuecontains "On Track"textNO
A3,U3:AA3Cell Valuecontains "On Track"textNO
P12:P13Cell Value="Coding"textNO
P12:P13Cell Value="Dev Completed"textNO
P12:P13Cell Value="Released"textNO
P12:P13Cell Valuecontains "On Track"textNO
P10:P11Cell Value="Coding"textNO
P10:P11Cell Value="Dev Completed"textNO
P10:P11Cell Value="Released"textNO
P10:P11Cell Valuecontains "On Track"textNO
P7:P8Cell Value="Coding"textNO
P7:P8Cell Value="Dev Completed"textNO
P7:P8Cell Value="Released"textNO
P7:P8Cell Valuecontains "On Track"textNO
Cells with Data Validation
CellAllowCriteria
I7:I8List=Phase!$A$2:$A$19
J7:J8List=Phase!$F$2:$F$9
I10:I13List=Phase!$A$2:$A$19
J10:J334List=Phase!$F$2:$F$9



The output I'm trying to target:
Template Copy.xlsm
ABCDEFGHIJKLMNOPQRSTUV
1TasksTicketsTrackerPriorityTeamResourceWeekTarget VersionTotal Person HrsDocumentationSpecResearchTDDCodingDev TestingCode Review/QATTest ScenarioTest CasesTestingReworkRisks/RemarksDeermine
2Sprint Tasks
3Development Tasks
4Module Documentations for Encounters12345400123454
5Test row 1123455Feature00123455
6BAMember 1
7Dev
8QA
9test row 2123456Feature00123456
10BAMember 1
11Dev
12QAMember 7
13Interest Payment123457FeatureHigh00123457
14BAMember 2
15Dev
16QAMember 8
17Third Party Billing123458FeatureNormal00123458
18BAMember 3
19Dev
20QAMember 9
21Technical Design Documentation00
22>>TDD - for test status123465Technical DesignNormal00123465
23BA
24DevMember 4
25QA
26>>TDD - for some random task123466Technical DesignNormal00123466
27BA
28DevMember 5
29QAMember 7
30>>TDD - Inquiry screen123467Technical DesignNormal00123467
31BA
32DevMember 6
33QAMember 8
34>>TDD - Upload file123468Technical DesignNormal00123468
35BA00
36DevMember 500
37QAMember 900
SprintPlan (2)
Cell Formulas
RangeFormula
I4:I5,I34:I37,I30,I26,I21:I22,I17,I13,I9I4=SUM(J4:T4)
T4:T5,T34:T37,T30,T26,T21:T22,T17,T13,T9T4=0.3*SUM(J4:S4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U21Cell Valuecontains "On Track"textNO
A21,W21:AC21Cell Valuecontains "On Track"textNO
U3:V3Cell Valuecontains "On Track"textNO
A3,W3:AC3Cell Valuecontains "On Track"textNO
Cells with Data Validation
CellAllowCriteria
E1:E3Any value



Here's my macro:
VBA Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,212,927
Messages
6,110,714
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top