Splitting a workbook in to multiple workbooks

[MarcWiseman]

I have yet another question regarding my Journal spreadsheets. First I will give you a few sample rows:

<table style="border-collapse: collapse; width: 833pt;" width="1110" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 139pt;" width="185" height="20">ANALYTICAL CHEMISTRY</td> <td style="width: 48pt;" width="64" align="right">2006</td> <td style="width: 48pt;" width="64" align="right">1.8739</td> <td style="width: 48pt;" width="64" align="right">4854</td> <td style="width: 48pt;" width="64" align="right">0.22279</td> <td style="width: 48pt;" width="64" align="right">0.002065</td> <td style="width: 48pt;" width="64" align="right">24451</td> <td style="width: 48pt;" width="64">ANALYTIC CHEMISTRY</td> <td style="width: 118pt;" width="157">ANAL CHEM</td> <td style="width: 48pt;" width="64">0003-2700</td> <td style="width: 48pt;" width="64" align="right">5.646</td> <td style="width: 48pt;" width="64">Sci</td> <td style="width: 48pt;" width="64">EA </td> <td style="width: 48pt;" width="64">EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JOURNAL OF CHROMATOG A</td> <td align="right">2006</td> <td align="right">0.89256</td> <td align="right">5598</td> <td align="right">0.12238</td> <td align="right">0.001247</td> <td align="right">16119</td> <td>ANALYTIC CHEMISTRY</td> <td>J CHROMATOGR A</td> <td>0021-9673</td> <td align="right">3.554</td> <td>Sci</td> <td>CO EA </td> <td>CO EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JOURNAL OF AGRICULTU</td> <td align="right">2006</td> <td align="right">0.75708</td> <td align="right">6276</td> <td align="right">0.11638</td> <td align="right">0.001222</td> <td align="right">14037</td> <td>ANALYTIC CHEMISTRY</td> <td>J AGR FOOD CHEM</td> <td>0021-8561</td> <td align="right">2.322</td> <td>Sci</td> <td>AH DW JY </td> <td>AH DW JY </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ANALYTICA CHIMICA AC</td> <td align="right">2006</td> <td align="right">0.85767</td> <td align="right">4056</td> <td align="right">0.085205</td> <td align="right">0.000874</td> <td align="right">10483</td> <td>ANALYTIC CHEMISTRY</td> <td>ANAL CHIM ACTA</td> <td>0003-2670</td> <td align="right">2.894</td> <td>Sci</td> <td>EA </td> <td>EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ELECTROPHORESIS </td> <td align="right">2006</td> <td align="right">0.96589</td> <td align="right">2456</td> <td align="right">0.058104</td> <td align="right">0.000584</td> <td align="right">6839</td> <td>ANALYTIC CHEMISTRY</td> <td>ELECTROPHORESIS</td> <td>0173-0835</td> <td align="right">4.101</td> <td>Sci</td> <td>CO EA </td> <td>CO EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JOURNAL OF CHROMATOG B</td> <td align="right">2006</td> <td align="right">0.70062</td> <td align="right">2965</td> <td align="right">0.050881</td> <td align="right">0.000543</td> <td align="right">7101</td> <td>ANALYTIC CHEMISTRY</td> <td>J CHROMATOGR B</td> <td>1570-0232</td> <td align="right">2.647</td> <td>Sci</td> <td>CO EA </td> <td>CO EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TALANTA </td> <td align="right">2006</td> <td align="right">0.79669</td> <td align="right">2172</td> <td align="right">0.042384</td> <td align="right">0.000441</td> <td align="right">5407</td> <td>ANALYTIC CHEMISTRY</td> <td>TALANTA</td> <td>0039-9140</td> <td align="right">2.81</td> <td>Sci</td> <td>EA </td> <td>EA </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PHYTOCHEMISTRY </td> <td align="right">2006</td> <td align="right">0.83836</td> <td align="right">1950</td> <td align="right">0.040042</td> <td align="right">0.000413</td> <td align="right">4370</td> <td>ANALYTIC CHEMISTRY</td> <td>PHYTOCHEMISTRY</td> <td>0031-9422</td> <td align="right">2.417</td> <td>Sci</td> <td>DE </td> <td>DE </td></tr></tbody></table>
Would it be possible to copy all the rows (through row N) with the same column 8 (H) (I know they are all analytical chemistry here but there are about 50 other categories) and put all the rows (and the header) for each category in a separate workbook? And I don't know if it's possible could it save the workbook under the same name as the category and in the excel 97-2003 format?

Thanks in advance,

Marc

 

[MarcWiseman]

Was this unclear or not possible?

 

[MarcWiseman]

?

 

[Norie]

Marc

Here's an example of splitting out data into multiple workbooks based on criteria in a column.

Perhaps you can adapt it for your needs.

Sub DistributeRowsToNewWBS()
Dim wbNew As Workbook
Dim wsData As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long
    
    Set wsData = Worksheets("Master (2)") ' name of worksheet with the data
    Set wsCrit = Worksheets.Add
    
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    ' column H has the criteria
    wsData.Range("H1:H" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    Set rngCrit = wsCrit.Range("A2")
    While rngCrit.Value <> ""
        Set wsNew = Worksheets.Add
        ' change N to reflect columns to copy
        wsData.Range("A1:N" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True
        wsNew.Name = rngCrit
        wsNew.Copy
        Set wbNew = ActiveWorkbook
        ' saves new workbook in path of existing workbook
        wbNew.SaveAs ThisWorkbook.Path & "\" & rngCrit
        wbNew.Close SaveChanges:=True
        Application.DisplayAlerts = False
        wsNew.Delete
        rngCrit.EntireRow.Delete
        Set rngCrit = wsCrit.Range("A2")
    Wend
    
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

 

[MarcWiseman]

Thanks for this shortly into the file I get a run time error on the following line:

wsNew.Name = rngCrit

also it did save a few files before the error message but they took me to the original master sheet when I clicked on them.

I have a list of the 50 categories in a separate file...would it simplify things if I put them in a separate sheet (in column A)?

 

[Norie]

Marc

What is the error message?

What I posted is really only an example of how you could approach this.

One problem could be that the data has invalid worksheet and or workbook names.

 

[MarcWiseman]

You're right it doesn't like the length of some of the category names

 

[Norie]

Marc

As far as I can recall the maximum length for a worksheet's name is 32 characters.

Don't know if there is such a limitation for workbook names, but with those you might run into problems with illegal filenames.

Is it possible that you could create another column with a formula that somehow parses the data to still leave unique values?

Than you could do the filtering on that column.

 

[MarcWiseman]

I'll try it.

Thanks for your time.

Marc