Can someone help? (urgently needed)

[rainx]

Hi, I have a problem here, I have a list of entries of which has different group names. I need to sort them into various files based on their group name. In a way, doing filter, However, my concern is my group names will vary every month and I also need the files to be save respectively at a place with their group name. Kinda tough, Hope someone would be able to provide some advice. Wondering if it can just filter those that are in the list into different files instead of providing a fixed list to do for all each time. Please Help!

Thanks!
<!-- / message --> <!-- sig --> __________________
Cheers
Rainx

 

[cornflakegirl]

Hi. You haven't given a lot of details, so it's hard to give code that will do exactly what you want. But below is some code that I wrote for someone else - hopefully it will provide a useful start point. When it refers to currency, read that as whatever the field is you'll be filtering on. This creates new workbooks, but doesn't save them.

Sub FilterMacroToNewBooks()
 
Dim ws As Worksheet
Dim wb As Workbook
Dim rCellCounter As Range
Dim sDataSheet As String
Dim sCurrencyField As String
Dim iCurrencyColumn As Integer
 
sDataSheet = "Data" 'change this to the name of your sheet holding the original data
sCurrencyField = "Currency" 'change this to the title of your currency field
iCurrencyColumn = 1 'change this to the column number of your currency column
 
Application.ScreenUpdating = False
 
Set wb = ActiveWorkbook
 
'Delete any existing currency sheets
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name <> sDataSheet Then ws.Delete
Next ws
Application.DisplayAlerts = True
 
'Create currency sheets
Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Temp"
Sheets(sDataSheet).Columns(iCurrencyColumn).AdvancedFilter Action:=xlFilterCopy, _
    criteriarange:="", copyToRange:=Sheets("Temp").Range("A1"), Unique:=True
'must include criteriarange - advanced filter "remembers" the last settings
 
'Filter on to each sheet
For Each rCellCounter In Sheets("Temp").Range("A1").CurrentRegion
    If rCellCounter.Value <> sCurrencyField Then
        Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))
        ws.Name = rCellCounter.Value
        ws.Range("a1").Value = sCurrencyField
        ws.Range("a2").Value = rCellCounter.Value
        Sheets(sDataSheet).Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            criteriarange:=ws.Range("A1:A2"), copyToRange:=ws.Range("A4"), Unique:=False
        ws.Rows("1:3").Delete
    End If
Next rCellCounter
 
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
 
For Each ws In Worksheets
    If ws.Name <> sDataSheet Then ws.Move
Next ws
 
wb.Sheets(sDataSheet).Activate 'just for aesthetics
Application.ScreenUpdating = True
 
End Sub

 

[rainx]

Hi firstly Thanks for your reply! Maybe after reading this, it might help you understand more...

I have a feeling my previous thread wasnt well understood, so I guess mayb I should show a sample data. Well my sample is as follow, basically, what I needa do is to group these entries into their respective groups, eg, apple, orange and pear. However, my issue is my Groups will vary each time I run a report. Is there any way I can group these entries based on their groups into individual files, or if not possible, sheets, named under their group name. Hope someone is able to help. THANKS ALOT!

<table x:str="" style="border-collapse: collapse; width: 330pt;" width="440" border="0" cellpadding="0" cellspacing="0"><col style="width: 126pt;" width="168"> <col style="width: 156pt;" width="208"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 126pt;" width="168" height="17">Column A</td> <td style="width: 156pt;" width="208">Column B</td> <td style="width: 48pt;" width="64">Column C</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-left: medium none;">asdf</td> <td class="xl24" style="border-left: medium none;" x:num="" align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfsaf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">312312</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asfdsdf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">313123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfasdf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3123113</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">sadfsaf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfsaf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">1213</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">fasfs</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Apple</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">fasff</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfasdf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">fsf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">safsfaf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">safsdfs</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">afsafa</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Orange</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">sdfasfa</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">fsfasfsf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asfdsfas</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfsf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">adfsf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">212</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asdfsaf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Pear</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">asfsf</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">123</td></tr></tbody></table>

 

[cornflakegirl]

Have you tried running the code I posted on your sample file? You'll have to amend two lines at the start of the code to match your setup:

sDataSheet = "Data" 'change this to the name of your sheet holding the original data
sCurrencyField = "Currency" 'change this to the title of the field you're filtering on eg Fruit

(Because the code uses Advanced Filter, your data will need column titles. If that's not possible, shout.)

 

[rainx]

Oh I understand alr! Thanks alot! Love you so much sweetie!~~

Thanks!

 

[rainx]

Can i ask u if it is possible to save the file as the name of the currency filtered out?

 

[cornflakegirl]

Try replacing

ws.Move

with

ws.SaveAs ActiveWorkbook.Path & "/" & ws.Name

(stolen from this thread http://www.mrexcel.com/archive2/65500/75857.htm and untested).

 

[cornflakegirl]

(sweetie? )

 

[DonkeyOte]

it got an answer didn't it

 

[rainx]

Haha.. er.. it dun work lei, it only split into sheets with the name. I need to be separated to workbooks. hmm?