I need to split data for each country into a new workbook with the headers as well. as the countries have different number of rows, how can I split according to the country. Also, sometimes there are remarks behind the country, making it more difficult to filter. I have tried to loop and count but it does not work. will appreciate any help. Thank you enter image description here

put on hold as too broad by Máté Juhász, PeterH, Pimp Juice IT, bertieb, VL-80 Dec 12 at 4:01

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Please share what you've tried and explain how exactly it doesn't work, will try to fix it. We won't make your code as superuser is not a free script writing service. – Máté Juhász Dec 7 at 7:02
  • Iterate by Country column and compare with previous value (from row 2, initial previous Country as empty string). Eqial? iterate next... not equal? select from current (if current and previous are not empty) until data end, delete, copy worksheet to ne workbook (name-Counry), undo delete, mark from row 2 to previous row, delete, continue iterate until Country is empty (no more data), close without save. That's all. – Akina Dec 7 at 7:04

I would like to suggest you VBA code(Macro), is the fastest method to Split or Filter Data from Source Workbook to another.

    Sub Copy_FilteredRange()

        Dim wb As Workbook, ws As Worksheet, rng As Range
        Dim CountryName As String

            Set wb = Workbooks("Source.xlsx")
            Set ws = wb.Sheets("Sheet1")
            Set rng = ws.Range("A1")

            Application.ScreenUpdating = 0

 CountryName = Application.InputBox(Prompt:="Country Name?", Type:=8)

 ws.ListObjects("DataTable").Range.AutoFilter Field:=2, Criteria1:=CountyrName, Operator:=xlFilterValues
            ws.AutoFilter.Range.Offset(1).Copy
Workbooks.Open 
Filename:="You file Path"
Worksheets("Sheet1").Select

ActiveSheet.Range("A1").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End Sub

How it works:

  • Select the Source Data and Convert it into Table and Name it,DataTable.
  • At the Source Workbook's Sheet 1, Press Alt+F11 to open the VB editor window.
  • Copy & Paste this code as Standard Module.
  • Finally RUN the Macro.
  • To make Macro dynamic, every time you RUN this code, Macro will prompt you to enter the new Country Name to Filter records.
  • Change the Target Workbook Name every time before you RUN the Macro, to Save Filtered Records in different Workbooks.

N.B.

  • I'm assuming that Sheet 1 of the Source Workbook has data to Filter, starts from Cell A1.
  • Source & Target Workbook, Sheet, Table Name & Data Range are editable.
  • You may also adjust Auto filter Field Nu.

Not the answer you're looking for? Browse other questions tagged or ask your own question.