Hi All, I have an excel data which is a tedious task, so i broke it down as steps to automate but Iam not good at custom VBA code. if the below conditions are met then need to copy the entire filtered table to a new sheet and next copy should pasted down to the first copied table, its a continues copy paste in the same sheet.
**Please see the image in attachment to see the data.
Basically the hack for calculations of number of copies are here : if Col6 is "Yes" then (Col2 x 3 sets) + (col3 x 4 sets) + (col4 x 1 sets) = total 8 sets of copies of filtered table. if Col6 is "No" then (Col2 x 2 sets) + (col3 x 4 sets) + (col4 x 1 sets) = total 7 sets of copies of filtered table.
Something is blank then count comes down example: if col2 is blank then col3 x 4 sets + Col4 x 1 = 5 sets of filtered table of all 7 columns should copy to another sheet.
Elaboration of the above summary is below:
a) Defined Conditions for non -blank cells: (do not copy hidden cells)
- Filter non blank cells of all Col2,Col3,Col4
- after filtering non blank cells of all three need check Col6 is Yes or No
- if col6 is "Yes" then we need to copy all those Non blank rows (not hidden rows) which are Yes to another sheet and the number of times it should copy the all filtered rows or table total of 8 sets.(meaning copy 8 times) and should write Col7 as "First" for first copy and "Second" for second copy etc.
- if col6 is "No" then should copy entire filtered rows 7 times or 7 sets.
b) Defined Conditions for blank and non -blank cells: (do not copy hidden cells):
- if col2 is blank but col3 and col4 is non-blank then entire filtered rows copy 5 times to new sheet mention "First" copy, "second copy in Col7 as it is to identify which copy(no need to check for Col6)
- if col2 and Col3 are blank but col4 is non-blank then entire filtered rows copy 1 time only.
- if col2 is non-blank but col3 and col4 are blank then copy entire thing only for 3 times if Col6 is "Yes" else copy only for 2 times.
- if col2 and col3 are non-blanks but col4 is blank then if col6 is "Yes" copy filtered rows for 7 times else for "No" copy 6 times.
- if col2 and col4 are non-blanks but col3 is blank then if col6 is "Yes" then copy 4 times else for "No" copy those only 3 times.
Bookmarks