Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    PA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reverse Pivot (97)

    I am trying to derive detail data from a crosstab sheet. I would like to create a new sheet with raw data in rows for import into a database via Access. I have length down the left, width across the top, and hours in the crosstab center cells. I need length, width, hours in columns. I have attached a sample of what I have and what I would like. Any help in automating this procedure via macro would be great.
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Pivot (97)

    The code below is one of the many possiblities to do what you want. Of course, you should redefine the ranges if you change the original datarange.

    <pre>Sub TransposeDataRange()
    Dim RHours As Range
    Dim RLength As Range
    Dim RWidth As Range
    Dim ROutput As Range
    Dim i As Integer, j As Integer
    Set RLength = Range("A5:A9")
    Set RWidth = Range("B4:F4")
    Set RHours = Range("B5:F9")
    Set ROutput = Range("A13")
    ROutput.Offset(0, 0) = "Length"
    ROutput.Offset(0, 1) = "Width"
    ROutput.Offset(0, 2) = "Hours"
    For i = 1 To RHours.Rows.Count
    For j = 1 To RHours.Columns.Count
    ROutput.Offset((i - 1) * RHours.Columns.Count + j, 0) = RLength.Cells(i).Value
    ROutput.Offset((i - 1) * RHours.Columns.Count + j, 1) = RWidth.Cells(j).Value
    ROutput.Offset((i - 1) * RHours.Columns.Count + j, 2) = RHours.Cells(i, j).Value
    Next j
    Next i
    End Sub
    </pre>


  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    PA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Pivot (97)

    Thanks. I am almost there. How do I change the ROutput range to create the output on a separate sheet (Sheet2)? I tried to change the ROutput range to Range("Sheet2!A1") and it only put the word "Length" on Sheet2 and nothing more.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Pivot (97)

    Set ROutput = Range("Sheet2!A13") should work, but you can also try
    Set ROutput = Sheets("Sheet2").Range("A13")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •