On Sep 1, 12:23*am, Joel wrote:
See if this code works. *I tried to make the code handle every case I could
think of to prevent errors. *This code is prety robust. *The code creates a
summary sheet. *if you run the code twice you must remove the summary sheet
manually.

Sub CreateSummary()
StartMonth = 8

Set SumSht = Sheets.Add(after:=Sheets(Sheets.Count))
SumSht.Name = "Summary"
'Create Header Row
For MonthNumber = 0 To 11
* *SumSht.Cells(1, MonthNumber + 4) = _
* * * MonthName(((StartMonth + MonthNumber - 1) Mod 12) + 1)
Next MonthNumber

SumRowCount = 1
With Sheets("01 Inventory")
* *LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* *For InventRowCount = 1 To LastRow
* * * If .Range("A" & InventRowCount) = "Item number" Then
* * * * *SumRowCount = SumRowCount + 1
* * * * *.Range("A" & InventRowCount & _
* * * * * * ":C" & InventRowCount).Copy _
* * * * * * Destination:=SumSht.Range("A" & SumRowCount)
* * * End If
* * * If IsDate(.Range("A" & InventRowCount)) Then
* * * * 'find the correct column for the month name
* * * * NameofMonth = .Range("B" & InventRowCount)
* * * * Inventory = .Range("C" & InventRowCount)
* * * * With SumSht
* * * * * *Set c = .Rows(1).Find(what:=NameofMonth, _
* * * * * * * LookIn:=xlValues, lookat:=xlWhole)
* * * * * *If c Is Nothing Then
* * * * * * * MsgBox ("Cannot find Month : " & NameofMonth)
* * * * * *Else
* * * * * * * .Cells(SumRowCount, c.Column) = Inventory
* * * * * *End If
* * * * End With
* * * End If
* *Next InventRowCount
End With
End Sub



" wrote:
On Aug 31, 6:01 pm, Joel wrote:
It is hard to give you a good answer because I don't know for how many items
you need to perform this conversion for and if you are doing it to multiple
items the spacing and header rows for each item. *There is one problem that
need to be fixed no matter which methoid you use. *Your month names either
need to be spellout completely or use the standard 3 letter abrevation for
each month name in both the source table and where you want the results to go.

It would also help to give the sheet name(s) and the rows and columns where
the data is located.

" wrote:
How can I get below info to show as:

A1 * * * * * * * * * * * * * * * B1 * * * * *C1
Item number * * * *894011 *Bag Poly w/snaps for 6x9 mats

Requirement date * Month * Calculated Opening Inv.
8/1/2008 * * * * * * * * *August * * * * *0
9/1/2008 * * * * * * * * September * * -624
12/1/2008 * * * * * * * *December * * *2,076
1/1/2009 * * * * * * * * January * * * 1,932
3/1/2009 * * * * * * * * March * * * * 1,452
================================================== =========================**=======
Like this

Aug. * *Sept * * *October * * * *Nov. * *Dec
Item number * * * *894011 *Bag Poly w/snaps for 6x9 mats * * * * * * *0
-624 * * * * *#N/A * * * #N/A * 2076

Some months are in order some of them are not the way you see above..

thanks
Vedat- Hide quoted text -

- Show quoted text -

Thank you, I know my data was short or not clear.

*My item numbers are 5 digits(B1747) that is why I put B1747100 and
formula.
I use this formula: =IF(B1747100,IF(B1758="August",I1758),""), if
August is not in the line, It does not work.

I have 6635 lines and trying to pull 485 items out of those 6635
lines. Spaces are specific.

Sheet name is 01 Inventory.

Do these info helps?
Vedat- Hide quoted text -

- Show quoted text -

Thank you very much, It worked.