Hi all,
I am hoping that the following is possible to be done with vba code. I have searched but am struggling to find code to cover all of this. I have broken this down into 3 separate stages below.
I have a set of data in an excel spreadsheet that runs from column A to Column K and 6000 rows. The columns will always be the same, but the rows can increase over time. A snapshot example of data below.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Nesling[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Dereck[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Ashely[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Reece[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
</tbody>[/TABLE]
What I am looking at achieving is this (probably with a separate macro for each part):
1) Firstly I need 7 rows inserted between each line so that for e.g. Row 1 will have David Jones, followed by 7 blank rows and on row 9 will be Steve Davies and so on to the last row of data (circa 6000 rows).
2) I then need the newly inserted 7 blank rows to be auto filled with the data of the row above for columns A to C only, so for e.g rows 2 to 8 will show David Jones 10.00 for columns A to C. Then row 9 will show Steve Davies 10.00 and I then need rows 9 to 16 to also show Steve Davies 10.00 and so on. I need this to be done for all data rows to the last row of data. Columns D to K do not need to be auto-filled with the years in the newly inserted blank rows.
3) The last part that I am hoping to achieve is to cut and transpose the years for each row in columns D to K and to paste down in column D.
This needs to be done to the last row of data.
So what I would expect to see is the e.g. below with no data in columns E to K and years in column D.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is a work project, so any help on this would be very, very gratefully received. I am an intermediate user of VBA and this has so far stumped me.
Keeping my fingers crossed on this one, so if you can assist it would be a massive help to me.
If you need any further clarification, please let me know.
Many thanks,
Barry.
I am hoping that the following is possible to be done with vba code. I have searched but am struggling to find code to cover all of this. I have broken this down into 3 separate stages below.
I have a set of data in an excel spreadsheet that runs from column A to Column K and 6000 rows. The columns will always be the same, but the rows can increase over time. A snapshot example of data below.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Nesling[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Dereck[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Ashely[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Reece[/TD]
[TD]Roberts[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
</tbody>[/TABLE]
What I am looking at achieving is this (probably with a separate macro for each part):
1) Firstly I need 7 rows inserted between each line so that for e.g. Row 1 will have David Jones, followed by 7 blank rows and on row 9 will be Steve Davies and so on to the last row of data (circa 6000 rows).
2) I then need the newly inserted 7 blank rows to be auto filled with the data of the row above for columns A to C only, so for e.g rows 2 to 8 will show David Jones 10.00 for columns A to C. Then row 9 will show Steve Davies 10.00 and I then need rows 9 to 16 to also show Steve Davies 10.00 and so on. I need this to be done for all data rows to the last row of data. Columns D to K do not need to be auto-filled with the years in the newly inserted blank rows.
3) The last part that I am hoping to achieve is to cut and transpose the years for each row in columns D to K and to paste down in column D.
This needs to be done to the last row of data.
So what I would expect to see is the e.g. below with no data in columns E to K and years in column D.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Davies[/TD]
[TD]10.0[/TD]
[TD]2025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is a work project, so any help on this would be very, very gratefully received. I am an intermediate user of VBA and this has so far stumped me.
Keeping my fingers crossed on this one, so if you can assist it would be a massive help to me.
If you need any further clarification, please let me know.
Many thanks,
Barry.