troubleshooting Question

Macro for changing structure of data in Excel

Avatar of Andreamary
Andreamary asked on
Microsoft OfficeVBAMicrosoft ExcelMicrosoft ApplicationsProgramming
5 Comments2 Solutions148 ViewsLast Modified:
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

I have attached the spreadsheet with sample data.

Thanks,
Andrea
EE_RestructureData.xlsm
ASKER CERTIFIED SOLUTION
Shums
Excel VBA Developer
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros