• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split files according to Country..

K

kundanlal

Member
Required to create report for each country with same sheet names and data for that country.
 

Attachments

  • DataSample.xlsx
    10.6 KB · Views: 19
hello!
Few questions regarding ur sample file:

Only Sheet A1 is to be seen in your file or all other sheets also matter?
Secondly you want to sort data country and vendor wise or only country wise.
 
All sheets of the files needs to be included in output file. The output file name be country.xls with same sheet name as of original file with the data of same country across all sheets. i.e. india.xls will consist of sheets named A1,B1,C1,D1 with a data of country India in A1,D1. Let the heading be repeated with blank details in B1,C1, Like wise..Pl. help in this.Thanks..I have a simple macro where only single sheet can be created with countrywise file If macro can ask user to create file country wise or vendor wise will also help.
 
Thanks Deepakji.. Actually, need to create different files accordingly to country wise as I don't want user to see other country data. can create different files with single sheet, but not able to manage the same with multiple sheets..
 
If there are few countries only then better to sort manually countrywise & then "save as" file giving country name. But automation is good choice. Shall give the code asap.
 
Thanks Vrunda..That's true for couple of occasions. If user needs to do this frequently with large file, automation is the best way which saves time and output would be 100% accurate. If u can put choice to create country-wise fle to specific field would be still better.. Thanks..
 
Thanks Vrunda.. This can be done.. Attached herewith a sample file which creates country wise report for sheet A1 only.

Actually, I was looking for output country-wise files with all sheets with the same sheet name as of original file.

Pl. see if you can help me in this.

Thanks..
 

Attachments

  • DataSample1.xls
    54 KB · Views: 11
tested & working!!!

Code: 
Option Explicit
Sub AASplit_in_A_Workbook()
    Dim MySheet As Worksheet, ws As Worksheet
    Dim MyRange As Range, i As Long, N As Workbook
    Dim UList As Collection, UListValue As Variant, c As Long
    Dim m As Range, cr As Range
     
Application.ScreenUpdating = False
c = 4 'Col D to filter
    Set MySheet = ActiveSheet
    If MySheet.AutoFilterMode = False Then Exit Sub
        Set MyRange = Range(MySheet.AutoFilter.Range.Columns(c).Address)
            Set UList = New Collection
            On Error Resume Next
                For i = 2 To MyRange.Rows.Count
                    UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
                Next i
            On Error GoTo 0
            For Each UListValue In UList
                Set N = Workbooks.Add(xlWBATWorksheet)
                    For Each ws In ThisWorkbook.Sheets
                        Set m = ws.Columns(4).Find("Country")
                        Set cr = m.Offset(-1, 0).CurrentRegion
                            m.AutoFilter c, UListValue
                            With N
                                 cr.SpecialCells(xlCellTypeVisible).Copy
                                 Sheets.Add().Name = ws.Name
                                 Sheets(ws.Name).Paste
                                 Cells.EntireColumn.AutoFit
                             End With
                        ws.AutoFilterMode = False
                    Next ws
                    N.SaveAs Filename:=Application.ThisWorkbook.Path & "\" & UListValue.Value
               N.Close False
            Next UListValue
   MySheet.Select
Set cr = Nothing
Set m = Nothing
Set MyRange = Nothing
Set MySheet = Nothing
Application.ScreenUpdating = True
End Sub
 
Dear Sir,

Thanks for help.. Could not login for so many days. Seen your code today.

But getting some problem at my end while running the code. The output files could not be generated.

Can it be possible for you to upload the same file where the code was successfully executed.

Regards,

Kundanlal
 
Check this before run the code

Code: 
If MySheet.AutoFilterMode = False Then Exit Sub
 

Attachments

  • DataSample1 (1).xls
    47.5 KB · Views: 6
Dear Sir,

Thanks for prompt reply.

Actually, I am a normal user of excel who uses excel menu driven keys and some shortcuts.. Not familar with the VBA codes/program language etc. Therefore, cannot understnad what you have told me to.

Will be greatful, if extend your help to sort out the issue.

Regards,

Kundanlal
 
Dear Sir,

One Last query.. Tried code on actual file (copy of test.xls).. It worked but the headers are missing in output files. However, the file generated first, only one sheet shows the header rows (AUM). Not able to locate the error. Would appreciate if give logic.

Attaching the original file and 3 out put files for your observation.

Please look into.

Regards,

Kundanlal
 

Attachments

  • CHINCHWAD.xlsx
    31.6 KB · Views: 2
  • PUNE.xlsx
    120.7 KB · Views: 2
  • Copy of test.xls
    67 KB · Views: 3
Sorry Once again..

I have filtered all sheets with heading. However, the first output file only consist of headers while rest starts with subject and data without heading..

Please suggest solution.

Regards,

Kundanlal
 
This error occurred due to auto filter range.
Now, Sheet1 also get deleted in new wb
Check with attached wb.
 

Attachments

  • Copy of test (1).xls
    75.5 KB · Views: 9
Dear Sir,

Many Thanks..

Sorry to trouble you once again.

Attached new file generated where the first filter value is Pune. The sheet viz AUM getting all data instead of filtered value=Pune. While other sheets are OK and rest of files are also OK.

Regards,

Kundanlal
 

Attachments

  • PUNE.xlsx
    10.8 KB · Views: 4
kundanlal said:
Dear Sir,

...................... The sheet viz AUM getting all data instead of filtered value=Pune. While other sheets are OK and rest of files are also OK.

Regards,

Kundanlal
Click to expand...

I just checked & didn't find any such issue so far!!
 

Attachments

  • PUNE.xlsx
    10.2 KB · Views: 4
Dear Sir,

I really admire you for you help extended to me.

Only one thing I would like to mention that the filter has to be applied above actual heading.i.e. row where Total heading is in the column F-G. I tried deleting this row and executed the code, same error occured. But when I added one blank line above acutal heading and filter with blank line, code got executed correctly. Alongwith acual output, one extra file UFC.XLS with blank details also created.

This is for your information.

Thank you very much..

Regards,

Kundanlal
 
You must log in or register to reply here.
Back
Top