Search multiple values in column A and create sheets based on value name

[realitydrm]

Hello. I'm looking for an automated solution to find multiple values instead of using the "Contains" function in Excel where I have to search one by one. I would like to be able to search column A for, Jane Doe, John Doe, William Doe, etc. and cut all rows that contain the name to a new sheet that is named based on the names mentioned above. I may have to search 20+ names. The data sheet would only contain what is left.

[Logit]

.


Option Explicit


Sub CreateSheetsFromAList()
Dim myCell As Range, myRange As Range


Set myRange = Sheets("Sheet1").Range("A2") 'Names List begins at B5
Set myRange = Range(myRange, myRange.End(xlDown))
Application.ScreenUpdating = False
For Each myCell In myRange
'If Not Evaluate("=ISREF('" & myCell.Value & "'!A1)") Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = myCell.Value
Sheets(Sheets.Count).Range("A1").Value = "Names"
myCell.EntireRow.Copy Sheets(Sheets.Count).Range("A2")
Sheets(Sheets.Count).Columns("A:A").AutoFit
myCell.EntireRow.Clear
'End If
Next
Application.ScreenUpdating = True
End Sub

[realitydrm]

Thanks for your help. The sheets are being created but the data is not coming with the sheet. I entered my names where you have "Names". I entered like this, "Jane", "john", etc. However the data associated with that name is not transfering. I'm getting a run time error. Also the data im searching starts in A2 all the way down for what could be hundreds of rows.

[Logit]

.
I cleaned up the macro code by removing the lines that were "commented out" and changed the one comment from "Names List begins at B5" to say "Names List begins at A2".
Making those edits has no affect on the code ... they were only comments and not used by VBA during the execution of the macro code. Here is the edited code :



Option Explicit


Sub CreateSheetsFromAList()
Dim myCell As Range, myRange As Range


Set myRange = Sheets("Sheet1").Range("A2") 'Names List begins at A2
Set myRange = Range(myRange, myRange.End(xlDown))
Application.ScreenUpdating = False
For Each myCell In myRange
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = myCell.Value
Sheets(Sheets.Count).Range("A1").Value = "Names"
myCell.EntireRow.Copy Sheets(Sheets.Count).Range("A2")
Sheets(Sheets.Count).Columns("A:A").AutoFit
myCell.EntireRow.Clear
Next
Application.ScreenUpdating = True
End Sub


#1 - When you receive the 'run time error' , what line is highlighted as causing the error ?

# 2 - Have you tried the download, without making any changes, to see how it runs on your machine ? If you receive an error
with the download file, what error is it and what line is being highlighted ?

[realitydrm]

Not working. My Sheet1 column A is called 'Attn List (Client) ' This field may contain other text values. I'm only looking for Jane Doe, John Doe, William Doe etc. I want to be able to define my names in a string and call them all at once. The error message tells me I typed and invalid name for a sheet or chart at the Sheets (Sheets.Count) .Name = myCell.Value

[Logit]

.
Change the line highlighted in red:



For Each myCell In myRange
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = myCell.Value
Sheets(Sheets.Count).Range("A1").Value = "Names" '<<------ Edit this line to the following --->> Sheets(Sheets.Count.Range("A1").Value = "Attn List (Client)"

[realitydrm]

Tried changing to add my names. . Not working.

[Logit]

.
To cut to the chase, attach your workbook for review. Do not include any confidential information.

[Tom Jones]

To avoid typing that name in the VBA code, you need to make a list somewhere (perhaps in another sheet) with names that need to be copied if there are other texts in the A column that should not be copied.

PS Logit's code work well if you need to create sheets and copied data from that specific name. For all names found n column A.