A Macro for Test each value in column a and copy matching values into new worksheets and name worksheets the value found in column A

[amin maaleki]

Hello
i am working a Data sheets that contains 215000 rows. as it shows in this below picture:
Column A : Country Abbreviation Name
Column B : Export Year
Column C : Export Code
Column D : Export Value in Dollar
Column E : Export Value in Rial
Column F : Export Weight
and Finally
Column G : Code Difinition

<table style="border-collapse: collapse; width: 542pt;" border="0" cellpadding="0" cellspacing="0" width="722"><tbody><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Excel.Sheet"><meta name="Generator" content="Microsoft Excel 12"><link id="Main-File" rel="Main-File" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip.htm"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;} tr {mso-height-source:auto;} col {mso-width-source:auto;} br {mso-data-placement:same-cell;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:178; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl66 {color:windowtext; font-size:5.0pt; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:center; vertical-align:middle;} .xl67 {color:windowtext; font-size:5.0pt; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:center; vertical-align:middle; border:.5pt solid windowtext;} --> </style><table style="border-collapse: collapse; width: 191pt;" border="0" cellpadding="0" cellspacing="0" width="252"> <col style="width: 22pt;" width="29"> <col style="width: 16pt;" width="21"> <col style="width: 22pt;" width="29"> <col style="width: 31pt;" width="41"> <col style="width: 34pt;" width="45"> <col style="width: 25pt;" width="33"> <col style="width: 41pt;" width="54"> <tbody><tr style="height: 8.25pt;" height="11"> <td class="xl67" style="height: 8.25pt; width: 22pt;" height="11" width="29">Country</td> <td class="xl67" style="border-left: medium none; width: 16pt;" width="21">year</td> <td class="xl67" style="border-left: medium none; width: 22pt;" width="29">Code</td> <td class="xl67" style="border-left: medium none; width: 31pt;" width="41">Dollar</td> <td class="xl67" style="border-left: medium none; width: 34pt;" width="45">Rial</td> <td class="xl67" style="border-left: medium none; width: 25pt;" width="33">Weight</td> <td class="xl67" style="border-left: medium none; width: 41pt;" width="54">Code Definition</td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ABW</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">271114</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2179575</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3825154125</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3632262</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ABW</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">280300</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2402</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4215510</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10010</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">AFG</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1992</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">40600</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">637.46</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">44622</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">390</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">AFG</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1992</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">60201</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">6836.64</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">478565</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4900</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">AND</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2003</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">390230</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">9373</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">74230200</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">20400</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ARE</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1992</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">140502</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">415131.53</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">29059207</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">431874</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ARE</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1992</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">150712</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">417.1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">29197</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">370</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">IND</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1994</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">91002</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">393510.57</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">688643500</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1091</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">IND</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1994</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">120302</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3693.75</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">6464062</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">29550</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">…</td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ZWE</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2008</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">871120</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">57750</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">576807000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10290</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 8.25pt;" height="11"> <td class="xl67" style="border-top: medium none; height: 8.25pt;" height="11">ZWE</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2008</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">871419</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">5314</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">53076232</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2657</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table></tbody></table>

i try different macros but i can not write one that "Test each value in column a and copy matching values (Column A to G and even if they were zero) into new worksheets and name worksheets the value found in column A. can anyone help me please, thanks in advance

• <!--
• -->

 

[VoG]

Hello and welcome to MrExcel.

take a look at Excel Explosion.

 

[amin maaleki]

Thank You MR. Excel So Much
i try that but i think this AddIns dont work with Excel 2007
my row are 125000 and Excel 2003 can not handle it

please help me

• <!--
• -->

 

[VoG]

The add-in does work in Excel 2007. I've tested it myself.

 

[amin maaleki]

you are right completely
but i don't know why it does not work in my

if it has an other way, please guide me


wishes
amin

• <!--
• -->

 

[VoG]

Try this

Sub CountryToSheet()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("A" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            With ws.Rows(1)
                .HorizontalAlignment = xlCenter
                With .Font
                    .ColorIndex = 5
                    .Bold = True
                End With
            End With
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[amin maaleki]

Mr. Excel
Sorry for taking your time
when i run Datapig excel Explosion 3.0 on my sheet it only making a sheet with name DPTMP1 and after that my excel do not work

thanks a lot for your attention

sincerely yours
amin

• <!--
• -->

 

[amin maaleki]

thanks a lot MR. Excel
thank with all of my good wishes that i have for you in my heart man
i dont know how write my feeling


best wishes for you
amin

• <!--
• -->