Macro for changing structure of data in Excel

Andreamary
Andreamary used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Roy CoxGroup Finance Manager

Commented:
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

Author

Commented:
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.

Can the macro be revised to do this?

Cheers,
Andrea
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi Andrea,

Try attached...
AndreaMary_RestructureData.xlsm
Roy CoxGroup Finance Manager
Commented:
Hi Andrea

Sorry

I've adjusted the code. Try this

Option Explicit

Sub TransferData()
    Dim oTblSource As ListObject, oTblMain As ListObject
    Dim iX As Integer
    Dim lRw As Long
    Set oTblSource = Sheets("Sheet1").ListObjects(1)
    Set oTblMain = Sheets("Tasks").ListObjects(1)

    With oTblMain
        For iX = 1 To oTblSource.ListColumns.Count Step 5    '
            lRw = .ListRows.Count + 1
            oTblSource.ListColumns(iX).DataBodyRange.Resize(, 5).Copy
            oTblMain.ListRows(1).Range.Cells(lRw, 2).PasteSpecial xlValues
        Next iX
    End With
    Application.CutCopyMode = False
End Sub

Open in new window

EE_RestructureData.xlsm
Andreamary

Author

Commented:
Thanks to you both!

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. :-)

Best regards,
Andrea

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial