should I use vba to prepare data to pivot

[Todd Frisch]

I have spread sheets that list part numbers down the left & then out to the
right there is quantities in 12 monthly fields like so ( this is a
simplified example)

ITEM retail list JAN FEB
MAR
ORANGE ball 7.99 5.25 450 450 3245
BLUE ball 6.99 5.03 875 520 6282
RED ball 14.99 8.19 175 696 6066

Below is basically data prepared for pivoting

ITEM retail list MONTH
ORANGE ball 7.99 5.25 450
ORANGE ball 7.99 5.25 450
ORANGE ball 7.99 5.25 3245
BLUE ball 6.99 5.03 875
BLUE ball 6.99 5.03 520
BLUE ball 6.99 5.03 6282
RED ball 14.99 8.19 175
RED ball 14.99 8.19 696
RED ball 14.99 8.19 6066

Is there a way to use vba to do this - I need to prepare data for pivoting

This would be a fantastic help to me if someone could make a suggestion

Thank You for your time Todd

[Tom Ogilvy]

This worked with your test data:

Sub OrgData()
Dim rng As Range, rng1 As Range
Dim cell As Range, cell1 As Range
Dim rngDest As Range
Dim rw As Long
Dim sh As Worksheet
Set rngDest = Worksheets("Sheet3").Range("A1")
rw = 2
For Each sh In ThisWorkbook.Worksheets
If UCase(Left(sh.Name, 6)) = "SHEET2" Then
Set rng = sh.UsedRange.Columns(1)
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
Set rng = rng.Cells
For Each cell In rng
Set rng1 = rng.Parent.Range(rng(1, 4), _
rng(1, 256).End(xlToLeft))
For Each cell1 In rng1
rngDest(rw, 1).Resize(1, 3).Value = _
cell.Resize(1, 3).Value
rngDest(rw, 4).Value = cell1.Value
rw = rw + 1
Next
Next
End If
Next
End Sub

Regards,
Tom Ogilvy

Todd Frisch <tfr...@knex.com> wrote in message
news:uC04xuL1AHA.1284@tkmsftngp03...

[Todd Frisch]

Very nice thank you!!!!!!!!!

I have a question - Can you point out what part of the code determines the
break point between list price and the months.

I sent a simplified example - in reality I have a part number ( the ball )
with about 10 or 12 columns before you get to the months. and I have to
then pick up 12 months of units and 12 months of dollars .

Also - I have information to the left of the part number that needs to stay
with the part number

This concept is very important (creating a pivot friendly data set) - If I
have to move the data on the left of the item number I can do that

Todd Frisch

"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:etp8ZAX1AHA.1848@tkmsftngp03...

[Tom Ogilvy]

There was a typo in the first version - fixed here - was about to go out of
town, so didn't have time to notice since the data "looked right" in the
short time I had to examine it. Anyway this works and I have indicated
where things are hard coded. (except for obvious things like sheet names).

Sub OrgData()
Dim rng As Range, rng1 As Range
Dim cell As Range, cell1 As Range
Dim rngDest As Range
Dim rw As Long
Dim sh As Worksheet
Set rngDest = Worksheets("Sheet3").Range("A1")
rw = 2
For Each sh In ThisWorkbook.Worksheets
If UCase(Left(sh.Name, 6)) = "SHEET2" Then

'Travels down column 1 Assumes headers in row1

Set rng = sh.UsedRange.Columns(1)
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
Set rng = rng.Cells

'Travels down column 1 Assumes headers in row1
' Starts in A2 and goes down

For Each cell In rng

'This goes to end of row and does End, Left Arrow, so the
' 4 here assumes the Months start in column 4
Set rng1 = rng.Parent.Range(cell(1, 4), _
cell(1, 256).End(xlToLeft))

For Each cell1 In rng1

' here is were it repeats the first 3 columns
' for each month - so the 3 indicates reproduce
' the first 3 columns

rngDest(rw, 1).Resize(1, 3).Value = _
cell.Resize(1, 3).Value

' the 4 here puts the monthly data in the
' 4th column of the destination location

rngDest(rw, 4).Value = cell1.Value
rw = rw + 1
Next
Next
End If
Next
End Sub

Regards,
Tom Ogilvy

Todd Frisch <tfr...@knex.com> wrote in message

news:#a#Ramj1AHA.2236@tkmsftngp02...

[Todd Frisch]

I really appreciate the detail - I want to learn this .

I will study it as soon as I come up for air from this project I am on.

Thanks Todd

"Tom Ogilvy" <twog...@email.msn.com> wrote in message

news:#fK4Fon1AHA.2256@tkmsftngp02...