I have a spreadsheet that contains extracted data from another source, and I need to change the structure of the data to a more usable format for output to a pivot table. As I will be receiving the data in this format on a regular basis and need to perform this restructuring task every time, I would like to automate this task with a macro.
Spreadsheet description:
Each WO task has same 5 column headings: WO#, WO_Type, Date, Min, and Min_Type, and the WO numbers are repeated in each 5-column section across the spreadsheet:
Column A to E: WO task 1
Column F to J: WO task 2
Column K to O: WO task 3
Column P to T: WO task 4
While the current spreadsheet has 455 rows, the number of rows will vary each time.
I would like a macro that does the following:
Copies data from A2 to E2 and down to last row containing data, and pastes this data into B2 of table in "Tasks" tab.
Copies data from F2 to J2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab
Copies data from K2 to O2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab
Copies data from P2 to T2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab
Microsoft OfficeVBAMicrosoft ExcelMicrosoft ApplicationsProgramming
Last Comment
Andreamary
8/22/2022 - Mon
Roy Cox
I think this does what you want, please check and let me know if it needs any revision.
Note I have removed all the unused rows in the destination table as this makes it extremely difficult to calculate where to paste the data. I have replaced CFS with ="CFS" to ensure this is copied down as data is added. EE_RestructureData.xlsm
Andreamary
ASKER
Hi Roy,
Thanks very much for improving the process as outlined. I ran the macro, and it transferred all the records from Column A to E (WO task 1), which was good, but it did not appear to transfer the remaining records for WO task 2 (Column F to J), etc. through to WO task 4. Sheet 1 should end up with 1816 rows of data, I believe.
Roy, thanks for the revised macro, it worked well with a couple of minor issues - the first row was blank, for some reason, and the date field came in numerically as opposed to formatted as a date.
Shums, your solution worked perfectly.
I did my best to award the points fairly, balancing Roy's quick response time and willingness to revise the first iteration vs. Shum's solution working right out of the gate. :-)
Note I have removed all the unused rows in the destination table as this makes it extremely difficult to calculate where to paste the data. I have replaced CFS with ="CFS" to ensure this is copied down as data is added.