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.
I would like to suggest you VBA code(Macro), is the fastest method to Split or Filter Data from
Source Workbook to
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
Tableand Name it,
- At the
Source Workbook's Sheet 1, Press
Alt+F11to open the VB editor window.
Copy & Pastethis code as
- To make Macro dynamic, every time you
RUNthis code, Macro will prompt you to enter the new
Country Nameto Filter records.
- Change the
Target Workbook Nameevery time before you RUN the Macro, to Save Filtered Records in different Workbooks.
- I'm assuming that
Sheet 1of the
Source Workbookhas data to Filter, starts from
- Source & Target Workbook, Sheet, Table Name & Data Range are editable.
- You may also adjust
Auto filter Field Nu.