Create file xls based on colums & populate data with criteria Solved/Closed

Question

Hi,

I need a macro that create some new file (txt or xls it doesen't matter) starting from an existing xls.

The existing xls named "Arretrati" has data from colum A to column AA, I have to create some new files using the data in the column "F-Intermediary Code" (named with the content of column F), and I have to copy into the new file all the data that have the same code.

In the original file arretrati I can have many rows for the same intermediary code, I need to create only one new file with the code as name, then I need to copy there all the data, from colum A to AA for this code.

Thanks all for any help.

TrowaD · Answer

Hi Steveryde,

Do you have anything against applying autofilter?

Filter on "F-Intermediary Code", copy entire sheet, open new workbook, paste data and save workbook as "F-Intermediary Code".

Best regards,
Trowa

Steveryde · Answer

Sorry, I'ven't understand.

I gave you an example, in the "original" file arretrati.xlsx I have the intermediary code in the column F (same code many rows). In the other colums I  have many other information (column A - Name, Column B Address, Column C policy, etc).
Ando so for the intermediary 0001 in the arretrati.xlsx I have 10 rows, then i need to create the file 0001.xlsx with 10 rows, for the intermediary 0002 35 rows, i need to create the file 0002.xlsx with the 35 rows....and so on.

I can do this with the autofilter?

TrowaD · Answer

Hi Steveryde,

Yeah, I thought maybe you wanted to do this with one criteria.

To know which criteria to look for I created a sheet "Filterlist" (create this sheet in your original file "arretrati.xlsx"). On this sheet I used column A to put the criteria's. So A1 = 0001, A2 = 0002 and so on.

Then I run the code below to:
- filter data based on the criteria's found in the "Filterlist" Sheet.
- copy/paste filter result in new workbook.
- save workbook as criteria's name and close workbook.

Try it and see if it yields the desired result.

Sub RunMe()
Dim x As Integer
Dim sValue As String

x = 1

Do
    sValue = Sheets("Filterlist").Cells(x, 1)

Cells.AutoFilter Field:=6, Criteria1:=sValue
    Cells.Copy
    
    Workbooks.Add
    ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial
    ActiveWorkbook.SaveAs ("C:\MyDocuments\" & sValue & ".xls") 'Make sure both filepath and extention are correct
    ActiveWorkbook.Close
    
    x = x + 1

Loop Until Sheets("Filterlist").Cells(x, 1) = vbNullString

Cells.AutoFilter

End Sub

Best regards,
Trowa

Steveryde · Answer

Hi Trowa,

Thanks a lot for your precious help.

I've changed the code you wrote for me because it doesn't run under my excel.

Here the code modified:

---------------------------------------------------------------------
Sub CreaFileIntermediario()

Dim x As Integer
Dim sValue, sPath As String
Dim My_Range As Range
Dim LastRow As Long

x = 2

sPath = ActiveWorkbook.Path

Sheets("Arretrati").Select
Range("A2").Select
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set My_Range = Range("A1:Q" & LastRow)
My_Range.Parent.Select

Do
        


    sValue = Sheets("Filterlist").Cells(x, 1)

    My_Range.Parent.AutoFilterMode = False
    My_Range.AutoFilter Field:=2, Criteria1:=sValue
    My_Range.Copy
    
    Workbooks.Add
    ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial
    ActiveWorkbook.SaveAs (sPath & "\" & sValue & ".xlsx")
    ActiveWorkbook.Close
    
    x = x + 1

Loop Until Sheets("Filterlist").Cells(x, 1) = vbNullString

Cells.AutoFilter

End Sub
---------------------------------------------------------------------

I hope that this code could help somebody else.

Regards

Create file xls based on colums & populate data with criteria

4 responses

Similar discussions

Subscribe To Our Newsletter!