How do I create a custom fields report

Video tutorial:

You can create a custom fields report by creating an "Order report". Within the order report there are separate Excel sheets with details information of the orders that were placed for a specific offer. In those sheets the custom field information is also available.

If you would like to have all order information in one sheet, so you can make your own reporting calculations you can use the following steps in Excel to create a combined sheet:

Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
Go to Insert → Module

Paste the following code into the module window:

Sub Combine()
    Dim sheetIndex As Integer
    Dim rowIndex As Integer
    Dim currentRowIndexOut As Long

    On Error Resume Next

    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(3).Activate
    Range("A2").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    Sheets(1).Select
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets(1).Cells(1, 1).Value = "Offer name"
    Sheets(1).Cells(1, 1).Font.Bold = True


    Sheets(1).Activate
    currentRowIndexOut = 2
    rowIndex = 3

    For sheetIndex = 4 To Sheets.Count

        Sheets(sheetIndex).Activate
        Range("A3").Select
        Selection.CurrentRegion.Select
        Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(2)

        Sheets(sheetIndex).Activate

        rowIndex = 3
        Do While Not IsEmpty(Sheets(sheetIndex).Cells(rowIndex, 1).Value)

            Sheets(1).Cells(currentRowIndexOut, 1).Value = Sheets(sheetIndex).Cells(1, 2).Value

            currentRowIndexOut = currentRowIndexOut + 1
            rowIndex = rowIndex + 1
        Loop


    Next

    Sheets(1).Activate
End Sub

Press F5 to run the code and wait until it finishes executing. When it finished a new worksheet named Combined will be displayed which contains all the data from the offer specification sheets.

Page tree

How do I create a custom fields report