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]

Hi & welcome to the board.
A few questions
1) What are the ranges & their sheet names?
2) Do the other sheets need to be created, or do they already exist?
3) If the sheets exist, do you want the existing data cleared, or should the new data be added below.

 

[eosride]

Hello and thanks for welcoming me.
Ranges:
G0001 through G0299
G0300 through G0999
G2000 through G2999
G3000 through G3999
H0000 through H0999
H1000 through H1999
I0000 through I0999

Sheet names:
G0001-G0299 Y&P
G0300-G0999 Y&P
G2 Y&P
G3 Y&P
H0 Y&P
HT Y&P
I0 Y&P

The sheets already exist with headers so pasting the data would be best.

 

[Fluff]

How about

Sub FilterCopy()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("G0001", "G0299", "G0001-G0299 Y&P", "G0300", "G0999", "G0300-G0999 Y&P", "G2000", "G2999", "G2 Y&P", "G3000", "G3999", "G3 Y&P", "H0000", "H0999", "H0 Y&P", "H1000", "H1999", "HT Y&P", "I0000", "I0999", "I0 Y&P")
   With Sheets("[COLOR=#ff0000]Data[/COLOR]")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Ary) Step 3
         .Range("A1:I1").AutoFilter 1, ">=" & Ary(i), xlAnd, "<=" & Ary(i + 1)
         .AutoFilter.Range.Offset(1).Copy Sheets(Ary(i + 2)).Range("A" & Rows.Count).End(xlUp).Offset(1)
      Next i
      .AutoFilterMode = False
   End With
End Sub

Change the sheet name in red to suit.

 

[eosride]

That did it, thank you so much!

Can I add another request now that this issue has finally been put to rest?

How would I sort sheets G2 Y&P, G3 Y&P, H0 Y&P, HT Y&P, I0 Y&P by column B (RSL-1)?

 

[Fluff]

How about

Sub FilterCopy()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("G0001", "G0299", "G0001-G0299 Y&P", "G0300", "G0999", "G0300-G0999 Y&P", "G2000", "G2999", "G2 Y&P", "G3000", "G3999", "G3 Y&P", "H0000", "H0999", "H0 Y&P", "H1000", "H1999", "HT Y&P", "I0000", "I0999", "I0 Y&P")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Ary) Step 3
         .Range("A1:I1").AutoFilter 1, ">=" & Ary(i), xlAnd, "<=" & Ary(i + 1)
         .AutoFilter.Range.Offset(1).Copy Sheets(Ary(i + 2)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         If i > 5 Then
            Sheets(Ary(i + 2)).Range("A1").CurrentRegion.Sort key1:=Sheets(Ary(i + 2)).Range("B1"), order1:=xlAscending, Header:=xlYes
         End If
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[eosride]

That did the trick with 1 exception. I wanted sheets G0001-G0299 Y&P, G0300-G0999 Y&P left out of the sort.

 

[Fluff]

They are left out of the sort.
The first sheet that is sorted is G2 Y&P.

 

[eosride]

I apologize, you are correct. I'm not sure what I was looking at. Thanks you again for all of your help!

 

[eosride]

Fluff, last request... I promise. After this done, could another macro be requested to cut the rows that contain M & Y in column B, (RSL-1) in sheets G2 Y&P, G3 Y&P, H0 Y&P, and copy them to existing sheets named G2 LOW Y&P, G3 LOW Y&P, H0 LOW Y&P? Again, all sheets have a header in row 1.