Splitting a workbook in to multiple workbooks

MarcWiseman

New Member
Joined
Jun 26, 2008
Messages
33
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jun 26, 2008
Messages
33

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2008
Messages
33

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top