Hi experts,

I am currently working on a report that is at present done manually, i am trying to automate this to save some time. For this I tried 'recording' a macro but that only works if all data is of same numbers (i.e. same numbers of rows and columns) and obviously not of use as data change everyday.

I have written some manual steps that i want to have in my macro , would appreciate if someone can kindly help me in creating a dynamic macro plz.

I ve excluded Step 1 as that was just copy paste from some other source in excel.


2. Format Painter  ‘TC test cycle with ID’ column and Apply on Column AE of ‘QC Extract’ Sheet in DER
3. DME#537458 excel ’TestSet_Extract’ sheet Custom Filter  ‘Folder’ Column by ‘(O2A)’
4. ‘TC with Linked Defects’ sheet  Insert New Column ‘N’ and Name it ‘Priority’
5. ‘TC with Linked Defects’ sheet  Column N (i.e. Priority)  Format Cells as ‘General’
6. Apply vlookup formula “J2,'QC Extract' AE:AW,19,false” in Column N
7. DER ‘QC Extract’ Sheet 
a. Filter Column ‘Status’ & remove ‘NA’ & ‘Passed’
b. Filter Column ‘CR Classification’ & Select ‘BU High’ & ‘IT High’
8. Delete Existing ‘Sheet 3’ if already there in DER
9. ‘TC with Linked Defects’ sheet Select All  Insert Pivot Table
10. A New ‘Sheet 3’ will be created with Pivot Table
11. In Sheet 3  Pivot Table  Field List include
a. Report Filter TC Status , TC Priority ,Cycle
b. Column Labels Order Type 2
c. Row Labels Product Family
d. Value Change Request
12. Copy entire Pivot table just below
13. In Sheet 3  Pivot Table 1  Apply Following Filters
a. TC Status  Select Passed
b. TC Priority  Select BU High & IT High
c. Cycle  Deselect Cy04
14. In Sheet 3  Pivot Table 2  Apply Following Filters
a. TC Status  Deselect Passed
b. TC Priority  Select BU High & IT High
c. Cycle  Deselect Cy04
15. Copy the Last Pivot table (i.e. Formula section) and Paste it as formulas in Sheet 3 under 2 tables to get new figures.
16. Copy the updated numbers on last section on Sheet 3 and Paste it “High Priority 2” Sheet and format the entire table.
17. Go back to Sheet 3 Change TC Priority to “Low” and “Low – Prioritised” in 1st 2 sections
18. Copy Section 3 and paste it “Low Priority” Section and do Formatting.
Thanks much in advance.