• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 91
  • Last Modified:

Macro for changing structure of data in Excel

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.

  • 2
  • 2
2 Solutions
Roy CoxGroup Finance ManagerCommented:
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.
AndreamaryAuthor 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?

ShumsDistinguished Expert - 2017Commented:
Hi Andrea,

Try attached...
Roy CoxGroup Finance ManagerCommented:
Hi Andrea


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

AndreamaryAuthor 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,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now