Divide excel sheet into separate files.

S

silverteck22

I have an excel file containing data separated by lot number. There ar
several rows and columns of data for each lot number (there are a se
number of columns, however there may be 1 to 30 rows of informatio
pertaining to each lot number). The lot number is listed in column A
and is repeated in each row in which data pertaining to that lot numbe
is stored.

I'm looking for a way to automatically select all rows containing
certain lot number and copy them into a new excel document. It woul
be nice if the file could automatically be saved as the lot number.
Since my master excel document contains all the information for 50 lo
numbers it would be nice if a program could go in and extract the dat
pertaining to each lot number into a new file, for each lot number i
the master document.

Is this possible? If so, how and in what language?

Thank you for your time and consideration
 
R

Ron de Bruin

Hi silverteck22

Try this in a test workbook
It will save the files in C:\

Sub Copy_With_AdvancedFilter()
Dim ws1 As Worksheet
Dim WBNew As Workbook
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Set ws1 = Sheets("Sheet1")
Set rng = ws1.Range("A1:G20")
'Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on the first column in the range (change this if needed)

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use this columns)
Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value
Application.ScreenUpdating = False
For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value

Set WBNew = Workbooks.Add(1)

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WBNew.Sheets(1).Range("A1"), _
Unique:=False
WBNew.SaveAs "C:\" & cell.Value & ".xls"
WBNew.Close False
Set WBNew = Nothing
Next
.Columns("IU:IV").Clear
Application.ScreenUpdating = True
End With
End Sub


You can also find a example here that copy each Lot number in his own sheet.
http://www.rondebruin.nl/copy5.htm
See the last macro
 
S

silverteck22

Thank you so much, the script worked great!

I have one more question, and this might be a long shot; I don't know.
In column c for each lot number there is basically a nested case of th
original issue. ie. lot number ?????? contains ??? rows of data an
within those rows of data (under column c) a routing number is listed.
Several rows within the lot number have the same routing number. I
the best case scenario the script would create a file name
"lotnumber"_"routingnumber".xls and contain only the rows correspondin
to that routing number contained in the corresponding lot number.

I also wanted to ask as a side note if there was any way to retain th
column widths from the original document, or tell each new document t
autofit the column width.

I don't know how difficult this would be to implement, if you coul
shed some light I would greatly appreciate it.

Thanks again for all your help
 
R

Ron de Bruin

Hi

Untested:

You can add a helper column in the workbook with
a formula like this =A1&C1
And filter on that column instead of column A

After the copy line before you save line you can use this
WBNew.Sheets(1).Columns.AutoFit
 
S

silverteck22

I've run some tests using that idea combined with a few others of min
and I think its going to work out. Thanks again
 
Top