Excel datasheet needs dividing into more datasheets

[arran tw]

I have a file that is sent to me on a regular basis, which is to be filtered
into its own purchase order number then copied in its own sheet with other
data, ie model number, serial number ect, there is about 120 rows 4 colums
per sheet, is their any way i can automate this process as there can be about
30 sheets.

 

[Joel]

The answer to your question is YES. there are tow ways of accomplishing the
task. One is to write a macro from scratch. You did not give enough
information to do this. See other postings for samples of how to post your
request

The 2nds way is to record a macro while you perform the required steps. to
record a macro go to the Tools Menu - Macro - Start Recording. Then perform
a required operartions. stop the macro when you are done. The macro can be
modified manually if changes are required.

 

[arran tw]

Thanks will give it a try , is there way link the macro to the filter as the
filter see's all the different purchase order numbers, if so create loop to
pick the p/o numerical order.

 

[Joel]

this code will cycle through each item of the filter list

Sub test()

'turn autofilter off
' 1 equals filter number not the column number
ActiveSheet.AutoFilterMode = False

'get unique items in column A
Columns("A:A").AdvancedFilter _
Action:=xlFilterInPlace, _
Unique:=True
'set variable to unique names
Set unique_cells = Columns("A:A").SpecialCells(Type:=xlCellTypeVisible)

'cycle through each unique name
For Each cell In unique_cells
'set number of columns to copy for each filter
Set FilterRange = Range("A:G")
'set filter to each member
FilterRange.AutoFilter Field:=1, Criteria1:=cell
'get the data to copy
Set CopyRange = Columns("A:A").SpecialCells(Type:=xlCellTypeVisible)
'copy dataq to sheet 2
CopyRange.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
Next cell

End Sub

 

[Ron de Bruin]

hi arran

See
http://www.rondebruin.nl/copy5.htm

 

[arran tw]

Hi Joel the code is great, it does what i want apart from generate a new
sheet for each unique number is that possible. Thanx your a star

 

[Ron de Bruin]

See
http://www.rondebruin.nl/copy5.htm