PDA

View Full Version : Macro to split one file into many based on criteria !!!



vatson
07-06-2015, 11:17 PM
Hi ,

I need a macro which can split my data based on the countries in the dump in to diffrent country files. the countries will be present in 3 tabs .

I have pasted the Column headers i need split it based on the D Column 'Country' , it shud keep all the data pertaining to the country & should delete rest of the other country data & same the file in the country name . Kindly help me with this macro

Regards
Vatson


Period
Client
Client Name
Country
Client Group Domcile Region
Segment
GAM ID (FAM ID)
GAM Name (FAM Name)
MPR
Product XSell Mapping
YTD Annualized Revenue
Total Revenue YTD

Kenneth Hobs
07-07-2015, 06:33 AM
Welcome to the forum!

Please attach a sample file. I don't know if the files is CSV, XLSX, or other. Also, I don't know what type of files you want to create.

If your input file is an Excel file, then I guess you mean that the tabs are spreadsheets. IF that is the case, would the output file have the same number of "tabs"?

If Country is in 3 "tabs", would those be combined to unique for each or for all? e.g. USA could be in all 3 "tabs" so would the output have USA in 3 "tabs" or would it be just one USA and only 1 "tab"?

vatson
07-07-2015, 08:21 PM
Hi Kenith,

I am unable to upload the file as its restricted access , to upload any file even a sample file too.

but all the 3 tabs will have the same data sets but for different months

File is in XLXS

but the headers would remain the same , which i had pasted in my initial request

Regards
Vatson

vatson
07-08-2015, 01:54 AM
Hi Kenneth,

Please find attached the sample file for your reference.

Regards
Vatson

vatson
07-08-2015, 03:44 AM
Hi Kenneth,

Have posted the file in the thread as request.

Regards
Vatson

vatson
07-09-2015, 08:01 PM
Hi ,

Any any one in the forum , kindly help me with this macro !

Regards
Vatson

Kenneth Hobs
07-09-2015, 08:08 PM
I haven't forgotton about this thread. It is a pretty custom need and will take some time. It is not that hard, just takes a little more time than I get to by tomorrow. If no one posts a solution by then, I will this weekend.

vatson
07-09-2015, 08:13 PM
Thank you sooo much Kenneth , hope to get some solution

vatson
07-12-2015, 08:09 PM
Hi Kenneth,

Hope yo had a good week end .Any luck ?????

Regards
Vatson

Kenneth Hobs
07-13-2015, 02:31 PM
Be sure to test on a backup copy of the file. In a Module:

Sub UniqueCountryFiles()
Dim i As Integer, a() As Range, c As Range, ws As Worksheet, nd() As Variant
Dim v As Variant, fso As Object, cWB As Workbook, pathWB As String, cFN As String

pathWB = ThisWorkbook.Path & "\" 'Path to copy country files to. Include trailing backlash.

Set fso = CreateObject("Scripting.FileSystemObject")

'Make array of range currentregion for worksheets with A1=Period.
For Each ws In Worksheets
If ws.Range("A1").Value2 = "Period" Then
i = i + 1
ReDim Preserve a(1 To i)
Set a(i) = ws.Range("A1").CurrentRegion
End If
Next ws

'Make array nd() of unique names in column D of each worksheet with A1="Period".
With CreateObject("scripting.dictionary")
For i = 1 To UBound(a)
For Each c In a(i).Range("D2:D" & a(i).Rows.Count).Cells
If Not .exists(c.Value2) Then .Add c.Value2, Nothing
Next c
Next i
nd() = .Keys
End With
'Debug.Print Join(nd(), vbLf) 'Show unique countries from Column D in Immediate window

'Copy master file and delete non-country data and sheets
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For i = 1 To UBound(nd)
cFN = pathWB & nd(i) & ".xlsb"
fso.CopyFile ThisWorkbook.FullName, cFN
Set cWB = Workbooks.Open(cFN)
'Filter each worksheet with A1="Period" by unique contry
For Each ws In Worksheets
With ws
ws.Select
If .Range("A1").Value2 = "Period" Then
.Range("A1").AutoFilter 4, "<>" & nd(i), xlAnd
.Range("A1").EntireRow.Hidden = True
.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Delete
.Range("A1").EntireRow.Hidden = False
.Range("A1").AutoFilter
Else
ws.Delete
End If
End With
Next ws
cWB.Close True
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "All countries have been exported to: " & pathWB
End Sub

vatson
07-13-2015, 10:13 PM
Hi Kenneth,

Thank you very much for your macro ,I am not well versed in VBA, Could you send me the macro which you had created using my file. I will edit it.

Regards
Vatson

vatson
07-14-2015, 12:25 AM
Hi Kenneth,

Thank you very much for your macro ,its working, I just pasted it as such, Thank you very much!
Regards