Problem with a macro

Posted by Marc on May 01, 2001 9:52 AM

I retreave orders from AS/400 for differents products.
But all the products are transfered on the same worksheet.
I would like having one worksheet by product and its corresponding order.
Is there any macro which is able to paste the first product with its in a worksheet, the second one in an other one etc...
Thanks for your help.
Marc

Posted by Dave Hawley on May 01, 2001 11:12 AM

Hi Marc

Yes this would certainly be possible. Try this code:

Sub PlaceOnSheets()
Dim i As Integer
Dim sName As String
Dim shtStart As Worksheet
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''
'Creates a new sheet for each Column
'heading in Row 1 of the ActiveSheet
'''''''''''''''''''''''''''''''''''''''''

Application.ScreenUpdating = False

'Set Worksheet Varaible to ActiveSheet
Set shtStart = ActiveSheet

'Loop through each Column of the ActiveSheet
'and create a new sheet named the same as the
'Column heading. Then fill Column A of the new sheet
'with the contents of Column i of shtStart
For i = 1 To WorksheetFunction.CountA(shtStart.Rows(1))
sName = shtStart.Cells(1, i)
Sheets.Add().Name = sName
ActiveSheet.Colulms(1) = shtStart.Columns(i)
Next i

Application.ScreenUpdating = True
End Sub

Dave

OzGrid Business Applications

Posted by Marc on May 02, 2001 1:41 AM

Thank you Dave, but I have one detail more...

Dave,
Everything would be OK if I received each product in a different column. But on my worsheet I have only two columns: one for the products and one for their orders. So I can't a worsheet for each columns. What I'd like is a worsheet by product and its order.
Sorry if I explained myself in a bad way.
Marc