I need some help. I get a few reports like this on a weekly basis the largest one only containing about 150 rows and 26 columns. What I would like to do is use a macro to add a few things to the bottom of each sheet. Each sheet is dynamic in its number of rows and columns.
<tbody>
</tbody>
These are the rows that I would like to add to the bottom of the sheet. The first two rows should repeat the same across to the last column below each date column. The date data always starts at row 13 but last row and column change week to week.
<tbody>
</tbody>
Any help would be greatly appreciated.
123465 | 30-Nov-2019 | 7-Jul-2119 | 3-Feb-2020 | 20-Jan-2020 | 30-Jun-2021 | 30-Jun-2020 | 7-Jul-2119 | 4-Oct-2020 | 27-Jun-2020 | 27-Dec-2019 |
123465 | 28-Dec-2019 | 7-Jul-2119 | 16-Jan-2020 | 6-Dec-2019 | N/A | 31-Jan-2020 | 7-Jul-2119 | 20-Mar-2020 | 27-Jun-2020 | 27-Dec-2019 |
123465 | 3-Jan-2020 | 7-Jul-2119 | 10-Jan-2019 | 26-Apr-2020 | 13-Dec-2020 | 30-Sep-2020 | 7-Jul-2119 | 8-Jan-2020 | N/A | N/A |
123465 | 31-Dec-2019 | 7-Jul-2119 | 17-Jun-2020 | 23-Dec-2019 | N/A | 30-Jun-2020 | 7-Jul-2119 | 26-Jul-2018 | N/A | N/A |
123465 | 29-Dec-2019 | 7-Jul-2119 | 17-Jun-2020 | 11-Apr-2020 | 7-Sep-2020 | 31-Dec-2019 | 7-Jul-2119 | 23-Oct-2020 | N/A | N/A |
123465 | 13-Dec-2019 | 7-Jul-2119 | 17-Jun-2020 | 3-Jan-2020 | N/A | 29-Feb-2020 | N/A | 26-Dec-2019 | N/A | N/A |
123465 | 18-Oct-2019 | 7-Jul-2119 | 23-Jan-2020 | N/A | 7-Sep-2020 | 29-Feb-2020 | 7-Jul-2119 | 28-Feb-2019 | 17-Oct-2018 | 17-Apr-2018 |
123465 | 2-Nov-2019 | 7-Jul-2119 | 23-Jan-2020 | 21-Mar-2020 | 20-Feb-2022 | 31-Jul-2020 | 7-Jul-2119 | 16-Jul-2021 | N/A | N/A |
123465 | 5-Jan-2020 | 7-Jul-2119 | 6-Feb-2020 | 9-Mar-2020 | 7-Sep-2020 | 30-Sep-2020 | 7-Jul-2119 | 24-Aug-2018 | 25-Jun-2019 | 25-Dec-2018 |
123465 | 26-Dec-2019 | 7-Jul-2119 | 19-Jun-2020 | 27-Mar-2020 | 6-Apr-2020 | 31-May-2020 | 7-Jul-2119 | 18-Oct-2018 | 8-Feb-2019 | N/A |
123465 | 2-Jan-2020 | 7-Jul-2119 | 27-May-2019 | 3-Apr-2020 | 5-Mar-2021 | 30-Sep-2020 | 7-Jul-2119 | 8-May-2020 | N/A | N/A |
M123465 | 4-Jan-2020 | 7-Jul-2119 | 10-Jan-2019 | 22-Mar-2020 | 27-Sep-2021 | 30-Sep-2020 | 7-Jul-2119 | 29-Dec-2017 | N/A | N/A |
M123465 | 2-Jan-2020 | 7-Jul-2119 | 19-Jun-2020 | 1-Mar-2020 | N/A | 31-Jul-2020 | 7-Jul-2119 | 18-Sep-2020 | 23-Oct-2018 | 23-Apr-2018 |
M123465 | 31-Dec-2019 | 7-Jul-2119 | 16-Jan-2020 | 5-May-2020 | 4-Apr-2021 | 31-Dec-2019 | 7-Jul-2119 | 26-Jul-2018 | N/A | N/A |
M123465 | 20-Dec-2019 | 7-Jul-2119 | 16-Jan-2020 | 24-Jan-2020 | 30-Mar-2020 | 31-Oct-2020 | 18-Jul-2119 | 20-Dec-2020 | 26-Jan-2018 | 26-Jul-2017 |
M123465 | 22-Dec-2019 | 7-Jul-2119 | 10-Jan-2019 | 17-Feb-2020 | 1-Apr-2021 | 31-Aug-2020 | 7-Jul-2119 | 22-Jun-2018 | 23-Oct-2019 | 23-Feb-2019 |
M123465 | 29-Dec-2019 | 7-Jul-2119 | 23-Jan-2020 | 25-Apr-2020 | 5-Mar-2021 | 30-Jun-2020 | 7-Jul-2119 | 8-May-2020 | N/A | N/A |
M123465 | 26-Dec-2019 | 7-Jul-2119 | 10-Jan-2019 | 2-Feb-2020 | 13-Dec-2020 | 31-Mar-2020 | 7-Jul-2119 | 8-Jan-2020 | N/A | N/A |
<tbody>
</tbody>
These are the rows that I would like to add to the bottom of the sheet. The first two rows should repeat the same across to the last column below each date column. The date data always starts at row 13 but last row and column change week to week.
Out of date | =COUNTIFS(B13:B18,"<="&TODAY(),A13:A18,"<>M*")+COUNTIFS(B13:B18,"N/A",A13:A18,"<>GAL*") |
Out of Date + M | =COUNTIFS(B13:B18,"<="&TODAY())+COUNTIFS(B13:B18,"N/A") |
total | =COUNTIFS(A13:A18,"*",A13:A18,"<>M*") |
total +M | =COUNTIFS(A13:A18,"*" |
<tbody>
</tbody>
Any help would be greatly appreciated.
Last edited by a moderator: