Split an Excel File into Multiple Files by Multiple Criteria [Using Macro/VBA]

K

kelz_dunks4life

New Member
Joined
Sep 13, 2014
Messages
5
Good Morning All,

I was wondering if there's a way to take an Excel file that can be split into multiple files by multiple criteria. I tried searching the forum, but couldn't find anything [or maybe I missed it].

Please see the example below.

The file needs to be split into multiple workbooks by the 'PT_Name' that ONLY contains information from 'UC_BC' that is either 171, 172, 173, or174.

For instance:

DAILY CENSUS MASTER.XLS

[TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]DT OF SVC[/TD]
[TD]STN[/TD]
[TD]RM/BED[/TD]
[TD]UB_CD[/TD]
[TD]CUR AC[/TD]
[TD]ADM DT[/TD]
[TD]LOS[/TD]
[TD]PT NAME[/TD]
[TD]NXT REV|DATE[/TD]
[TD]MRN[/TD]
[TD]ACCT#[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]AGE[/TD]
[TD]ADM DX[/TD]
[TD]SVC[/TD]
[TD]TYPE[/TD]
[TD]ATTEND MD[/TD]
[TD]TOTAL CHGS[/TD]
[TD]FC[/TD]
[TD]INS1[/TD]
[TD]POLICY#1[/TD]
[TD]APPROVAL#1[/TD]
[TD]INS2[/TD]
[TD]POLICY#2[/TD]
[TD]APPROVAL#2[/TD]
[TD]INS3[/TD]
[TD]POLICY#3[/TD]
[TD]APPROVAL#3[/TD]
[TD]TOT|DAYS[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E326-A[/TD]
[TD="align: right"]123[/TD]
[TD]K[/TD]
[TD="align: right"]9/8/2014[/TD]
[TD="align: right"]4[/TD]
[TD]COUNT, DOES NOT[/TD]
[TD="align: right"]9/9/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$40,244.20[/TD]
[TD]HS[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E335-A[/TD]
[TD="align: right"]123[/TD]
[TD]K[/TD]
[TD="align: right"]9/8/2014[/TD]
[TD="align: right"]4[/TD]
[TD]COUNT, DOES NOT[/TD]
[TD="align: right"]9/15/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$42,512.40[/TD]
[TD="align: right"]1[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E338-A[/TD]
[TD="align: right"]123[/TD]
[TD]K[/TD]
[TD="align: right"]9/10/2014[/TD]
[TD="align: right"]2[/TD]
[TD]COUNT, DOES NOT[/TD]
[TD="align: right"]9/12/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$10,687.25[/TD]
[TD]AE[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E344-A[/TD]
[TD="align: right"]171[/TD]
[TD]K[/TD]
[TD="align: right"]7/11/2014[/TD]
[TD="align: right"]63[/TD]
[TD]ONE, PLAYER[/TD]
[TD="align: right"]7/15/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$784,728.79[/TD]
[TD]E[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E340-A[/TD]
[TD="align: right"]171[/TD]
[TD]K[/TD]
[TD="align: right"]8/6/2014[/TD]
[TD="align: right"]37[/TD]
[TD]ONE, PLAYER[/TD]
[TD="align: right"]9/13/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$463,681.65[/TD]
[TD="align: right"]12[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E336-A[/TD]
[TD="align: right"]172[/TD]
[TD]J[/TD]
[TD="align: right"]9/11/2014[/TD]
[TD="align: right"]1[/TD]
[TD]TWO,PLAYER[/TD]
[TD][/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$2,964.25[/TD]
[TD]UH[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E343-A[/TD]
[TD="align: right"]173[/TD]
[TD]K[/TD]
[TD="align: right"]9/8/2014[/TD]
[TD="align: right"]4[/TD]
[TD]THREE,PLAYER[/TD]
[TD="align: right"]9/9/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$28,745.45[/TD]
[TD]KA[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E325-A[/TD]
[TD="align: right"]173[/TD]
[TD]K[/TD]
[TD="align: right"]9/7/2014[/TD]
[TD="align: right"]5[/TD]
[TD]THREE,PLAYER[/TD]
[TD="align: right"]9/12/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$37,135.10[/TD]
[TD="align: right"]11[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E342-A[/TD]
[TD="align: right"]174[/TD]
[TD]K[/TD]
[TD="align: right"]8/15/2014[/TD]
[TD="align: right"]28[/TD]
[TD]FOUR, PLAYER[/TD]
[TD="align: right"]8/19/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$279,051.21[/TD]
[TD]HS[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


And split certain contents into multiple files saved by the PT Name:


  • PT Name that only contains UB_CD as 171, 172, 173, and 174

ONE,PLAYER.XLS

[TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]DT OF SVC[/TD]
[TD]STN[/TD]
[TD]RM/BED[/TD]
[TD]UB_CD[/TD]
[TD]CUR AC[/TD]
[TD]ADM DT[/TD]
[TD]LOS[/TD]
[TD]PT NAME[/TD]
[TD]NXT REV|DATE[/TD]
[TD]MRN[/TD]
[TD]ACCT#[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]AGE[/TD]
[TD]ADM DX[/TD]
[TD]SVC[/TD]
[TD]TYPE[/TD]
[TD]ATTEND MD[/TD]
[TD]TOTAL CHGS[/TD]
[TD]FC[/TD]
[TD]INS1[/TD]
[TD]POLICY#1[/TD]
[TD]APPROVAL#1[/TD]
[TD]INS2[/TD]
[TD]POLICY#2[/TD]
[TD]APPROVAL#2[/TD]
[TD]INS3[/TD]
[TD]POLICY#3[/TD]
[TD]APPROVAL#3[/TD]
[TD]TOT|DAYS[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E344-A[/TD]
[TD="align: right"]171[/TD]
[TD]K[/TD]
[TD="align: right"]7/11/2014[/TD]
[TD="align: right"]63[/TD]
[TD]ONE, PLAYER[/TD]
[TD="align: right"]7/15/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$784,728.79[/TD]
[TD]E[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E340-A[/TD]
[TD="align: right"]171[/TD]
[TD]K[/TD]
[TD="align: right"]8/6/2014[/TD]
[TD="align: right"]37[/TD]
[TD]ONE, PLAYER[/TD]
[TD="align: right"]9/13/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$463,681.65[/TD]
[TD="align: right"]12[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

TWO,PLAYER.XLS

[TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]DT OF SVC[/TD]
[TD]STN[/TD]
[TD]RM/BED[/TD]
[TD]UB_CD[/TD]
[TD]CUR AC[/TD]
[TD]ADM DT[/TD]
[TD]LOS[/TD]
[TD]PT NAME[/TD]
[TD]NXT REV|DATE[/TD]
[TD]MRN[/TD]
[TD]ACCT#[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]AGE[/TD]
[TD]ADM DX[/TD]
[TD]SVC[/TD]
[TD]TYPE[/TD]
[TD]ATTEND MD[/TD]
[TD]TOTAL CHGS[/TD]
[TD]FC[/TD]
[TD]INS1[/TD]
[TD]POLICY#1[/TD]
[TD]APPROVAL#1[/TD]
[TD]INS2[/TD]
[TD]POLICY#2[/TD]
[TD]APPROVAL#2[/TD]
[TD]INS3[/TD]
[TD]POLICY#3[/TD]
[TD]APPROVAL#3[/TD]
[TD]TOT|DAYS[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E336-A[/TD]
[TD="align: right"]172[/TD]
[TD]J[/TD]
[TD="align: right"]9/11/2014[/TD]
[TD="align: right"]1[/TD]
[TD]TWO,PLAYER[/TD]
[TD][/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$2,964.25[/TD]
[TD]UH[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

THREE,PLAYER.XLS

[TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]DT OF SVC[/TD]
[TD]STN[/TD]
[TD]RM/BED[/TD]
[TD]UB_CD[/TD]
[TD]CUR AC[/TD]
[TD]ADM DT[/TD]
[TD]LOS[/TD]
[TD]PT NAME[/TD]
[TD]NXT REV|DATE[/TD]
[TD]MRN[/TD]
[TD]ACCT#[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]AGE[/TD]
[TD]ADM DX[/TD]
[TD]SVC[/TD]
[TD]TYPE[/TD]
[TD]ATTEND MD[/TD]
[TD]TOTAL CHGS[/TD]
[TD]FC[/TD]
[TD]INS1[/TD]
[TD]POLICY#1[/TD]
[TD]APPROVAL#1[/TD]
[TD]INS2[/TD]
[TD]POLICY#2[/TD]
[TD]APPROVAL#2[/TD]
[TD]INS3[/TD]
[TD]POLICY#3[/TD]
[TD]APPROVAL#3[/TD]
[TD]TOT|DAYS[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E343-A[/TD]
[TD="align: right"]173[/TD]
[TD]K[/TD]
[TD="align: right"]9/8/2014[/TD]
[TD="align: right"]4[/TD]
[TD]THREE,PLAYER[/TD]
[TD="align: right"]9/9/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$28,745.45[/TD]
[TD]KA[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E325-A[/TD]
[TD="align: right"]173[/TD]
[TD]K[/TD]
[TD="align: right"]9/7/2014[/TD]
[TD="align: right"]5[/TD]
[TD]THREE,PLAYER[/TD]
[TD="align: right"]9/12/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$37,135.10[/TD]
[TD="align: right"]11[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
FOUR,PLAYER.XLS

[TABLE="width: 3053"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]DT OF SVC[/TD]
[TD]STN[/TD]
[TD]RM/BED[/TD]
[TD]UB_CD[/TD]
[TD]CUR AC[/TD]
[TD]ADM DT[/TD]
[TD]LOS[/TD]
[TD]PT NAME[/TD]
[TD]NXT REV|DATE[/TD]
[TD]MRN[/TD]
[TD]ACCT#[/TD]
[TD]DOB[/TD]
[TD]SEX[/TD]
[TD]AGE[/TD]
[TD]ADM DX[/TD]
[TD]SVC[/TD]
[TD]TYPE[/TD]
[TD]ATTEND MD[/TD]
[TD]TOTAL CHGS[/TD]
[TD]FC[/TD]
[TD]INS1[/TD]
[TD]POLICY#1[/TD]
[TD]APPROVAL#1[/TD]
[TD]INS2[/TD]
[TD]POLICY#2[/TD]
[TD]APPROVAL#2[/TD]
[TD]INS3[/TD]
[TD]POLICY#3[/TD]
[TD]APPROVAL#3[/TD]
[TD]TOT|DAYS[/TD]
[/TR]
[TR]
[TD="align: right"]9/12/2014[/TD]
[TD]3E[/TD]
[TD]E342-A[/TD]
[TD="align: right"]174[/TD]
[TD]K[/TD]
[TD="align: right"]8/15/2014[/TD]
[TD="align: right"]28[/TD]
[TD]FOUR, PLAYER[/TD]
[TD="align: right"]8/19/2014[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]9999999[/TD]
[TD]NIC[/TD]
[TD]IP[/TD]
[TD]KELZ_DUNKS4LIFE[/TD]
[TD="align: right"]$279,051.21[/TD]
[TD]HS[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Is this something possible? I've suggested using a Pivot Table, but I'd rather find out from any of you if this is something that cannot be done. I hope I was clear and I appreciate your help.

Kels
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Give this a try, it worked OK in xl2007 and should work on earlier versions. It is assumed the code will be run from the master workbook. Copy the code below to code module 1 in the master file.
Code: 
Sub multFile()
Dim wb1 As Workbook, wbk As Workbook, sh As Worksheet, lr As Long
Set wb1 = ThisWorkbook
Set sh = wb1.Sheets(1)
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
    For i = 171 To 174
        If Application.CountIf(sh.Range("D2:D" & lr), i) > 0 Then
            Set wb = Workbooks.Add
            sh.UsedRange.AutoFilter 4, i
            sh.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy wb.Sheets(1).Range("A2")
            sh.Rows(1).Copy wb.Sheets(1).Range("A1")
            sh.UsedRange.AutoFilter
            fPath = ThisWorkbook.Path
            If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
            wb.SaveAs fPath & "UB_CD" & i
            wb.Close False
        End If
    Next
End Sub
 
Upvote 0
Hi JLGWhiz,

Excellent! It worked!

Just one question. It is possible to have the file saved under each PT Name? I appreciate your help!
 
Upvote 0
kelz_dunks4life said:
Hi JLGWhiz,

Excellent! It worked!

Just one question. It is possible to have the file saved under each PT Name? I appreciate your help!
Click to expand...

Try this modified version
Code: 
Sub multFile2()
Dim wb1 As Workbook, wb As Workbook, sh As Worksheet, lr As Long
Set wb1 = ThisWorkbook
Set sh = wb1.Sheets(1)
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
    For i = 171 To 174
        If Application.CountIf(sh.Range("D2:D" & lr), i) > 0 Then
            Set wb = Workbooks.Add
            sh.UsedRange.AutoFilter 4, i
            sh.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy wb.Sheets(1).Range("A2")
            sh.Rows(1).Copy wb.Sheets(1).Range("A1")
            sh.UsedRange.AutoFilter
            fPath = ThisWorkbook.Path
            If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
            wb.SaveAs fPath & wb.Sheets(1).Range("H2").Value & i
            wb.Close False
        End If
    Next
End Sub
 
Upvote 0
Thanks, again. It worked. I just realized that when I tested it on the real file [it contains confidential information], it'll only create 4 files, each file containing all of the '171', '172', '173', and '174'. Sorry to be a pest, but is it possible to get the data by PT_Name, but keep the criteria in tact? I needed to have the each PT Name have it's own file that contains either the four UB_CD. For instance, if there were 50 unique PT_Name that have either 171, 172, 173, or 174, there'll be 50 files. I really appreciate the help.
 
Upvote 0
kelz_dunks4life said:
Thanks, again. It worked. I just realized that when I tested it on the real file [it contains confidential information], it'll only create 4 files, each file containing all of the '171', '172', '173', and '174'. Sorry to be a pest, but is it possible to get the data by PT_Name, but keep the criteria in tact? I needed to have the each PT Name have it's own file that contains either the four UB_CD. For instance, if there were 50 unique PT_Name that have either 171, 172, 173, or 174, there'll be 50 files. I really appreciate the help.
Click to expand...


Sorry for the double post, but if it's possible to do the same with just a Pivot Table filtering only the four UB_CD's and it's much easier, that'd be appreciated as well.
 
Upvote 0
kelz_dunks4life said:
Sorry for the double post, but if it's possible to do the same with just a Pivot Table filtering only the four UB_CD's and it's much easier, that'd be appreciated as well.
Click to expand...

I think this will do it.
Code: 
Sub multFile3()
Dim wb1 As Workbook, wb As Workbook, sh As Worksheet, lr As Long, tSh As Worksheet, c As Range
Set wb1 = ThisWorkbook
Set sh = wb1.Sheets(1)
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
Sheets.Add After:=ThisWorkbook.Sheets(Sheets.Count)
Set tSh = ActiveSheet
    For i = 171 To 174
        If Application.CountIf(sh.Range("D2:D" & lr), i) > 0 Then
            sh.UsedRange.AutoFilter 4, i
            sh.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy tSh.Cells(Rows.Count, 1).End(xlUp)(2)
            sh.Rows(1).Copy tSh.Range("A1")
            sh.UsedRange.AutoFilter
        End If
    Next
lc = tSh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
tSh.Range("H2:H" & lr).AdvancedFilter xlFilterCopy, , tSh.Cells(1, lc + 1), True
    With tSh
        For Each c In .Range(.Cells(2, lc + 1), .Cells(Rows.Count, lc + 1).End(xlUp))
            .UsedRange.AutoFilter 8, c.Value
            Set wb = Workbooks.Add
            .Range("H2:H" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy wb.Sheets(1).Range("A2")
            tSh.Rows(1).Copy wb.Sheets(1).Range("A1")
            fPath = ThisWorkbook.Path
            If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
            wb.SaveAs fPath & c.Value
            wb.Close False
        Next
    End With
Application.DisplayAlerts = False
sh.Columns(lc + 1).Delete
tSh.Delete
Application.DisplayAlerts = True
End Sub
 
Upvote 0
Delete this line of code.
Code: 
sh.Columns(lc + 1).Delete
The temp column was used in the temporary sheet which is deleted entirely, so the line is not needed.
 
Upvote 0
JLGWhiz said:
Delete this line of code.
Code: 
sh.Columns(lc + 1).Delete
The temp column was used in the temporary sheet which is deleted entirely, so the line is not needed.
Click to expand...

Thanks as always, JLGWhiz.

I'm able to save, but every time I add the new data, it'll give me the alert that the 'X' file already exists and if I want to save. I do want it to save automatically and I've tried the VBA Codes on google to disable it but it won't work. Do you have any suggestions? Maybe I'm missing something. By the way, I've been also getting assistance from here as well.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Run Macro using batch file
Replies
0
Views
1K
Fatman003
F
K
  • Question Question
Split one table into multiple sheets based on criteria
2
Replies
14
Views
3K
Kyosti
K
V
  • Question Question
VBA coding to autofilter the data ( with some criteria), when the data starts from row 3
Replies
0
Views
436
Vishnu Kariappa
V
C
  • Question Question
Macro to move row data to columns
2
Replies
11
Views
1K
hiker95
H
B
  • Question Question
Split master excel into multiple files based on date
Replies
0
Views
707
bipul
B

Forum statistics

Threads
1,222,149
Messages
6,164,236
Members
451,882
Latest member
Bigtop

Share this page

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
Back
Top