Move rows to sheets based on alpha numeric value

[eosride]

Greetings,

I'm officially stumped on this one. I've searched numerous threads and cannot find what I'm looking for. I'd like a macro created that will copy or paste, whichever is less time consuming, entire rows into other sheets based on a range of alpha numeric characters found in column A. In this example, Sheet1 contains my data. I'd like the rows with PRIME ranges G0001 through G0299 moved to sheet G0001-G0299 Y&P. The process would need to be repeated for each additional prime range until Sheet1 is empty and all data is moved to the corresponding sheets. Thanks in advance for all of your help!

[TABLE]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Prime[/TD]
[TD="align: center"]RSL-1[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]Ven-Stk #[/TD]
[TD="align: center"]RSL-2[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]RSL-3[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]G0011[/TD]
[TD="align: center"]K5179[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]fnwkqntke1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]36[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]G0290[/TD]
[TD="align: center"]K5174[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]fnwkqntke23[/TD]
[TD="align: center"]K5176[/TD]
[TD="align: center"]73[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]140[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]G0312[/TD]
[TD="align: center"]K5068[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]fnwkqntke24[/TD]
[TD="align: center"]K5081[/TD]
[TD="align: center"]232[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]304[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]G0330[/TD]
[TD="align: center"]J4011[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]fnwkqntke25[/TD]
[TD="align: center"]J4209[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]71[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]G0590[/TD]
[TD="align: center"]K5279[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]fnwkqntke46[/TD]
[TD="align: center"]K5286[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]185[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]G0598[/TD]
[TD="align: center"]J5028[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]fnwkqntke47[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]G2127[/TD]
[TD="align: center"]J8273[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]fnwkqntke48[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]G2225[/TD]
[TD="align: center"]K4172[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]fnwkqntke49[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]G2853[/TD]
[TD="align: center"]J8189[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]fnwkqntke73[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]33[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]G3074[/TD]
[TD="align: center"]Y4021[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]fnwkqntke74[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]G3873[/TD]
[TD="align: center"]J9321[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]fnwkqntke94[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]H0029[/TD]
[TD="align: center"]Y4268[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]fnwkqntke95[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]H0935[/TD]
[TD="align: center"]Y4011[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]fnwkqntke123[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]H1021[/TD]
[TD="align: center"]M4337[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]fnwkqntke124[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]H1960[/TD]
[TD="align: center"]J6052[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]fnwkqntke156[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]I0073[/TD]
[TD="align: center"]Y4138[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]fnwkqntke157[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]I0763[/TD]
[TD="align: center"]M4027[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]fnwkqntke181[/TD]
[TD="align: center"]M4197[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]14[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

 

[Fluff]

How about

Sub FilterCopy()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("G2 Y&P", "G2 LOW Y&P", "G3 Y&P", "G3 LOW Y&P", "H0 Y&P", "H0 LOW Y&P")
   For i = 0 To UBound(Ary) Step 2
      With Sheets(Ary(i))
         If .AutoFilterMode Then .AutoFilterMode = False
            .Range("A1:I1").AutoFilter 2, "M*", xlOr, "Y*"
            With .AutoFilter.Range.Offset(1)
               .Copy Sheets(Ary(i + 1)).Range("A" & Rows.Count).End(xlUp).Offset(1)
               .EntireRow.Delete
            End With
         .AutoFilterMode = False
      End With
   Next i
End Sub

 

[eosride]

That was it. Thank you!

 

[Fluff]

Glad to help & thanks for the feedback

 

[eosride]

Fluff,
Sorry to revive an old post but I just recently ran into an issue.

I keep getting an autofilter error that causes excel to crash. I was able to run in debug mode 1 time before it crashed on me. I'm not able to attach the picture but I did post a link for the error. Thanks in advance!

https://imgur.com/a/iVVG7qJ

 

[Fluff]

Have you changed the code since it last worked?

 

[eosride]

I have not. It is part of a bigger script that contains 11 macros.

 

[Fluff]

In that case have you many any changes to the layout of the sheet?

 

[eosride]

I haven't made any changes within the last 2 months to the layout of the sheet. Not sure why I just started receiving the error.

 

[Fluff]

The only reason that I can think for that code to stop working is
a) The macro has been changed
b) The sheet has been changed

What is the value of i when the code fails?

 

[eosride]

Sorry, not sure what you mean by value of I. Here's a sample of the sheet right before it fails.

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Prime[/TD]
[TD="align: center"]RSL-1[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]Ven-Stk #[/TD]
[TD="align: center"]RSL-2[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]RSL-3[/TD]
[TD="align: center"]Cases[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]G0011[/TD]
[TD="align: center"]K5149[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]gmfkslnmg 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]24[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]G0016[/TD]
[TD="align: center"]K7410[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]gmfkslnmg 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]G0030[/TD]
[TD="align: center"]K6081[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]gmfkslnmg 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]G0035[/TD]
[TD="align: center"]K5214[/TD]
[TD="align: center"]106[/TD]
[TD="align: center"]gmfkslnmg 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]106[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]G0040[/TD]
[TD="align: center"]J4208[/TD]
[TD="align: center"]179[/TD]
[TD="align: center"]gmfkslnmg 5[/TD]
[TD="align: center"]J4210[/TD]
[TD="align: center"]185[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]364[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]G0067[/TD]
[TD="align: center"]J4006[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]gmfkslnmg 6[/TD]
[TD="align: center"]K5068[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]218[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]G0080[/TD]
[TD="align: center"]K5144[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]gmfkslnmg 7[/TD]
[TD="align: center"]K5147[/TD]
[TD="align: center"]374[/TD]
[TD="align: center"]K5158[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]454[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]G0086[/TD]
[TD="align: center"]K5256[/TD]
[TD="align: center"]152[/TD]
[TD="align: center"]gmfkslnmg 8[/TD]
[TD="align: center"]K5259[/TD]
[TD="align: center"]201[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]353[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]G0117[/TD]
[TD="align: center"]K6097[/TD]
[TD="align: center"]381[/TD]
[TD="align: center"]gmfkslnmg 9[/TD]
[TD="align: center"]K6100[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]420[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]G0130[/TD]
[TD="align: center"]J4017[/TD]
[TD="align: center"]158[/TD]
[TD="align: center"]gmfkslnmg 10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]158[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]G0136[/TD]
[TD="align: center"]K6079[/TD]
[TD="align: center"]261[/TD]
[TD="align: center"]gmfkslnmg 11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]261[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]G0137[/TD]
[TD="align: center"]J4137[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]gmfkslnmg 12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]35[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]G0150[/TD]
[TD="align: center"]J4323[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]gmfkslnmg 13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]42[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]G0155[/TD]
[TD="align: center"]J4331[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]gmfkslnmg 14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]G0174[/TD]
[TD="align: center"]K5332[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]gmfkslnmg 15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]G0212[/TD]
[TD="align: center"]K5284[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]gmfkslnmg 16[/TD]
[TD="align: center"]K5133[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]71[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]G0217[/TD]
[TD="align: center"]K6083[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]gmfkslnmg 17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]63[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]G0230[/TD]
[TD="align: center"]K5083[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"]gmfkslnmg 18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]113[/TD]

</tbody>

Sheet1​