Data distribution

[DOF2001]

Hello to all!

Can anyone give a hand, i have this macro that breack up data into tabs base on a specific column values:

Sub DistributeRows1()

Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim i As Long

Set wsAll = Worksheets("Global")
LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add

wsAll.Range("O1:O" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRowCrit

Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("A2")
wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
CopyToRange:=wsNew.Range("A1"), Unique:=False
wsCrit.Rows(2).Delete
Next i
Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True

End Sub

is possible to modify this code to breack into files instead of sheets, this new file will be placed into a specific folderin hte same directory.
Also i will nedd to do the opposite, meaning after analyst work on this new created files i will need to pull them together into one master file.


Thanks in advance.

 

[Norie]

Well to create new workbooks rather than sheets is pretty straightforward, the code just needs adapted a bit.

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("Global")
    Set wsCrit = Worksheets.Add
    
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    wsData.Range("O1:O" & LastRow).AdvancedFilter action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    Set rngCrit = wsCrit.Range("A2")
    While rngCrit.Value <> ""
        Set wsNew = Worksheets.Add
        wsData.Range("A1:E" & LastRow).AdvancedFilter action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True
        wsNew.Name = rngCrit
        wsNew.Copy
        Set wbNew = ActiveWorkbook
        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

As is this creates the new workbooks in the same directory as the workbook the code is in.

 

[DOF2001]

UPDATE

Thanks, but i still having some problems, firs i do not know where the new files are been created is a way to create the files in the same directory i have the file i'm working on, also the new files should be named as the value on hte criteria we are breaking them . EX:

Age GROUP BUS MRP Mat Material
< 9 Months PBC ABC 11 1152620
< 9 Months PBC ABC 11 1167003
< 9 Months PBC ABC 12 1207758
9 to 12 Months PBC ABC 11 1144899
9 to 12 Months PBC DBC 5 1159358
> 12 Months PBC MOS 11 1004469
> 12 Months PBC MOS 11 1012177
> 12 Months PBC MYG 11 1127510
> 12 Months PBC MYG 11 1132399
> 12 Months PBC QPB 11 1133178
> 12 Months PBC QPB 11 1152118

In this i want to brak it base on column 3(MRP), the result wourl be 5 new files ( ABC,DBC,MOS,MYG,QPB)

tHNKAS IN ADVANCE

 

[DOF2001]

Thanks! i got it working the way i need it , Just one more thing, how can i reverse it, meaning, get all hte data from each file into one main file.

Thanks

 

[DOF2001]

Norie, ca we reverse this macro ????????????

 

[Norie]

Why do you need to?

It doesn't actually touch the original file.

 

[DOF2001]

I need to do the opposite of what the macro does; ex

i need to pull the data from the new files created and combined into one master file.

Just a reverse of the macro.

 

[Norie]

That's actually a totally different thing and would require totally different code.

That code would also be specific to where the files are located, what worksheets you want to combine, how the data is structured etc

Post back with more information and I'll see if I can come up with something, but in the meantime I would suggest a board search for consolidation.

 

[DOF2001]

Ok Here is what i need to do:

I have one folder with a file Named "Risk" and a sub folder with many new files, each file has only one sheet named same as the file name;ex
Fiel name DSI will have one excel sheet named DSI. All the files have the same number of columns( same headings) so what i need is to update the main file "Risk" with the data from each sub files. i only need to update 4 columns from each file we can say columns 3,6,9,12.

PS: maybe is easy to just copy the contents of the whle sheet into the main file.

Thanks in advance

 

[Norie]

I think it might be best if you started a new thread for this.

PS Did you try a board search? If you do I'm sure you'd get plenty of hits because what you want to do is a common request.

Mind you there isn't really a 'generic' solution.