copy paste specific content

[excelnoobhere]

I'm looking for a code that will copy certain columns and paste them into a new sheets that will be created based on cell value.
for example lets say I have a huge list of data in cell A contain
A
5555-01-120-121
5555-01-120-123
5555-02-120-255
5555-05-150-220

I would like to be able to search through all of an and if A contain -01- then that gets copied that row's columns (A,B,O) into a new sheet columns (A,B,C) titled -01-, and if it contains -02- then creates a new sheet and does the same and so on for -05-

basically I want to split these data based on the value I have in red

how can I go about this?

any help would be much appreciated

 

[Fluff]

And the error message?

 

[excelnoobhere]

And the error message?

are you talking about when adding this

MsgBox "|" & Ky & "|"
if so,
the error is "|-01-|"

the other error is

run-time error 1004
application-defined or object-defined error

 

[Fluff]

I cannot replicate that error with your test file, however does this solve the problem

Sub excelnoobhere()
   Dim Cl As Range
   Dim Uniq As String
   Dim Ky As Variant
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Master")
   Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("A3", Ws.Range("A" & Rows.Count).End(xlUp))
         If IsNumeric(Left(Cl, 5)) Then
            Uniq = Mid(Cl, 6, 4)
            If Uniq <> "" Then .Item(Uniq) = Empty
         End If
      Next Cl
      For Each Ky In .Keys
         Ws.Range("A2:O2").AutoFilter 1, "*" & Ky & "*"
         Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
         Intersect(Ws.AutoFilter.Range.EntireRow, Ws.Range("A:B,O:O")).Copy Range("A1")
      Next Ky
      Ws.AutoFilterMode = False
   End With
End Sub

 

[excelnoobhere]

I cannot replicate that error with your test file, however does this solve the problem

Sub excelnoobhere()
   Dim Cl As Range
   Dim Uniq As String
   Dim Ky As Variant
   Dim Ws As Worksheet

   
   Set Ws = Sheets("Master")
   Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("A3", Ws.Range("A" & Rows.Count).End(xlUp))
         If IsNumeric(Left(Cl, 5)) Then
            Uniq = Mid(Cl, 6, 4)
            If Uniq <> "" Then .Item(Uniq) = Empty
         End If
      Next Cl
      For Each Ky In .Keys
         Ws.Range("A2:O2").AutoFilter 1, "*" & Ky & "*"
         Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
         Intersect(Ws.AutoFilter.Range.EntireRow, Ws.Range("A:B,O:O")).Copy Range("A1")
      Next Ky
      Ws.AutoFilterMode = False
   End With
End Sub

no sir still same error.
its weird first it was working on that sheet i made up and not the original. now its not working on either one

 

[Fluff]

Not sure what to suggest, it works fine for me on your sample files.

 

[Kenneth Hobson]

The first run on your test file works fine. As I explained, error 1004 is shown on a 2nd run as the worksheet name it is trying to add, already exists. It tells you that.

Code can easily be added to skip it or do something else...

 

[excelnoobhere]

The first run on your test file works fine. As I explained, error 1004 is shown on a 2nd run as the worksheet name it is trying to add, already exists. It tells you that.

Code can easily be added to skip it or do something else...

its not, it is the only page. I'm also manually deleting the pages.
and i have also tried it on a fresh workbook

 

[excelnoobhere]

Not sure what to suggest, it works fine for me on your sample files.

I somehow got it working. I have another question if you don't mind.

I have a template sheet that I would like to follow for every sheet that is created. This sheet can stay on the workbook and just recopy itself and rename.

I would like for it to start inputting information starting at row 6 however,
A would go into C
O would go into A
B would go into D

is that possible?

Thank you so much again

 

[Kenneth Hobson]

You have to post a workbook that has the problem if you want us to troubleshoot it at this point. We have tested your first example with Fluff's code and it "worked" as expected.

 

[Fluff]

As this is now a totally different question, you will need to start a new thread
Thanks