Data distribution

DOF2001

Active Member
Joined
Jan 28, 2005
Messages
310
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Well to create new workbooks rather than sheets is pretty straightforward, the code just needs adapted a bit.
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("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

Active Member
Joined
Jan 28, 2005
Messages
310
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

Active Member
Joined
Jan 28, 2005
Messages
310
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

Active Member
Joined
Jan 28, 2005
Messages
310

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why do you need to?

It doesn't actually touch the original file.:)
 

DOF2001

Active Member
Joined
Jan 28, 2005
Messages
310

ADVERTISEMENT

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

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

Active Member
Joined
Jan 28, 2005
Messages
310
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

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

Forum statistics

Threads
1,136,260
Messages
5,674,697
Members
419,520
Latest member
Jennifer4Dillon

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