bypassing an error with find function

[mdinino]

I am very dust at this and trying to write something that will help with saveing time... This code works great unless, the name it is searching for is not found, I would like to figure out how to tell it to bypass to next section of code if it does not find the name


here is my code....

Sub Macro1()
'
Sheets("PerformX Management").Select
Cells.Find(What:="Anthony Piccolo", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Anthony Piccolo").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End Sub



I repeat this code with all the names i need to find, so it is long, but this is the basic code.

I think there is a way to acomplish what I want to do, but I am not sure how it works, I think it is something like


If Dir(??what to put here??) = "??" Then
MsgBox fname & " not found."
Else
' code if file exists
End If


Thanks for the help

Mike

 

[Jonmo1]

Welcome to the board...

Try

Sub Test()
Dim ws As Worksheet, MyRange As Range
Set ws = Sheets("PerformX Management")
 
ON Error Resume Next
Set MyRange = ws.Cells.Find(What:="Anthony Piccolo", After:=ws.Cells(1,1), LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error Goto 0
 
If Not MyRange Is Nothing Then
    ws.Range(MyRange, MyRange.End(xlToRight)).Copy Sheets("Anthony Piccolo").Range("A" & Rows.Count).End(xlup).Offset(1)
End If

 

[mdinino]

Well thanks for the help but it didnt seem to do what i wanted, infact I kept getting other errors.. but after several hours trying and trying, this seemed to have worked... what do you guys think? to risky?

Sub Macroif()
On Error Resume Next
Sheets("PerformX Management").Select
Cells.Find(What:="Alejandro Velasquez", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Value = "Alejandro Velasquez" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Alejandro Velasquez").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
End Sub

Thanks Mike

 

[Norie]

Mike

That code will probably still error when nothing is found, but the On Error Resume Next could be masking that.

The code jomno posted should work - if nothing is found it won't cause an error.

What will happen is MyRange will be Nothing, which you can then check in the code after the find.

How did you try that code and what other errors were you getting when you did?

 

[mdinino]

I am not sure how to explain it yet, I am not sure how the dim works and how to look back at the range, but the trouble i was having with it is, the code I gave is repeated many times with different names, and it seemed to hang up on every different name that was not found, when I put solution code that was given, it shot me to debug.. still confused..

The code I posted just a few min ago, or my fix seems that if there is an error, it ignores, then it checks the value to see if it is correct, and if it is it performs the next step, but if it isnt, it moves on the the next name in the list.
There was probally a eaiser way to write this, but like I said, I am so dusty at this, sad sad sad... but I will find my way back!

Thanks so much though

Mike

 

[mdinino]

I spoke to soon, it seems erratic and doesnt perform as expected, I think
it may help if I give the bigger picture...








Code

<TABLE>
Sub Macroif()
On Error Resume Next
Sheets("PerformX Management").Select
Cells.Find(What:="Alejandro Velasquez", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Alejandro Velasquez" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Alejandro Velasquez").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Anthony Piccolo", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Anthony Piccolo" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Anthony Piccolo").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Benjamin Nemnich", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Benjamin Nemnich" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Benjamin Nemnich").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Billie Mounce, Jr.", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Billie Mounce, Jr." Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Billie Mounce, Jr.").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Brian Oubre", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Brian Oubre" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Brian Oubre").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Bryan Beaupre", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Bryan Beaupre" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Bryan Beaupre").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Chris Johnson", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Chris Johnson" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chris Johnson").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Cody Jones", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Cody Jones" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cody Jones").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Dave Anthony", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Dave Anthony" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Dave Anthony").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="David Griffith", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "David Griffith" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("David Griffith").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Derek Jensen", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Derek Jensen" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Derek Jensen").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Duane Fleck", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Duane Fleck" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Duane Fleck").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Eric Landon", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Eric Landon" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Eric Landon").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Eric Oslund", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Eric Oslund" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Eric Oslund").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Ezra Vaughn", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Ezra Vaughn" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Ezra Vaughn").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Ian Schjeldahl", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Ian Schjeldahl" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Ian Schjeldahl").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="James Hernandez", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "James Hernandez" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("James Hernandez").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="James Rodriguez", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "James Rodriguez" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("James Rodriguez").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Kraig Eichelberger", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Kraig Eichelberger" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Kraig Eichelberger").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Leonardo Farias", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Leonardo Farias" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Leonardo Farias").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Lloyd Robinson", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Lloyd Robinson" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lloyd Robinson").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Mario Saroni", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Mario Saroni" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mario Saroni").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Michael Brown", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Michael Brown" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Michael Brown").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Michael Hagler", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Michael Hagler" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Michael Hagler").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Mike Burk", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Mike Burk" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mike Burk").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Norman Howard", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Norman Howard" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Norman Howard").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Paige Libey-Couch", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Paige Libey-Couch" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Paige Libey-Couch").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Paul Lowrie", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Paul Lowrie" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Paul Lowrie").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Paul Wilcox", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Paul Wilcox" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Paul Wilcox").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Robert Brault", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Robert Brault" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Robert Brault").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If
Sheets("PerformX Management").Select
Cells.Find(What:="Robert Gage", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Robert Gage" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Robert Gage").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Steven Strong", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Steven Strong" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Steven Strong").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Terry Coleman", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Terry Coleman" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Terry Coleman").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

Sheets("PerformX Management").Select
Cells.Find(What:="Thomas Hill", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Selection.Text = "Thomas Hill" Then
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Thomas Hill").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else: Resume Next
End If

End Sub


<TBODY></TBODY></TABLE>




Any help would be great, Thanks


Mike

 

[Norie]

Mike

I'm pretty sure you don't need all that code.

You seen to be repeating the same find over and over with only the name you are looking for changing.

Also the name of the destination worksheet always seem to be the name you are looking for.

I think you should probably use some sort of list for the names.

Then you could use a loop to go through the list and do the search and, if the name is found the copy.

 

[Jonmo1]

This works for me, without having to repeat the code over and over for each name

This assumes that a sheet already exists for each name.

Sub Test()
Dim ws As Worksheet, MyRange As Range, MyNames As Variant, MyVal As Variant
 
'Put your list of names here
MyNames = Array("Anthony Piccolo", "Name2", "Name3")
 
Set ws = Sheets("PerformX Management")
 
For Each MyVal In MyNames
    On Error Resume Next
    Set MyRange = ws.Cells.Find(What:=MyVal, After:=ws.Cells(1, 1), LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0
 
    If Not MyRange Is Nothing Then
        ws.Range(MyRange, MyRange.End(xlToRight)).Copy Sheets(MyVal).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next MyVal
End Sub

 

[mdinino]

I am not sure what I am doing wrong, but I pasted your code into a new module, and added the names, but I does not do anything when I rund it.. going kinda crazy. Do I need to do something to turn on error handling?
arghhh

Code:



Sub Test()
Dim ws As Worksheet, MyRange As Range, MyNames As Variant, MyVal As Variant

'Put your list of names here
MyNames = Array("Alex, Velasques", "Anthony Piccolo", "Benjamin Nemnich", "Billie Mounce, Jr.", "Brian Oubre", "Bryan Beaupre", "Chris Johnson", "Cody Jones", "Dave Anthony", "David Griffith", "Derek Jensen", "Duane Fleck", "Eric Landon", "Eric Oslund", "Ezra Vaughn", "Ian Schjeldahl", "James Hernandez", "James Rodriguez", "Kraig Eichelberger", "Leonardo Farias", "Lloyd Robinson", "Mario Saroni", "Michael Brown", "Michael Hagler", "Mike Burk", "Norman Howard", "Paige Libey-Couch", "Paul Lowrie", "Paul Wilcox", "Robert Brault", "Robert Gage", "Steven Strong", "Terry Coleman", "Thomas Hill")

Set ws = Sheets("PerformX Management")

For Each MyVal In MyNames
On Error Resume Next
Set MyRange = ws.Cells.Find(What:=MyVal, After:=ws.Cells(1, 1), LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error GoTo 0

If Not MyRange Is Nothing Then
ws.Range(MyRange, MyRange.End(xlToRight)).Copy Sheets(MyVal).Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next MyVal
End Sub

Thanks again!

 

[mdinino]

So I think i figured it out, I had cells hidden on the sheets it was copying to, and I think it could not find where to copy becuase cell were hidden and I had it looking for that next available cell, is there a fix for that? or should I just leave it the way it is?

PS... Jonmo 1, that code works sooooo very nicely and much smoother then my previouse code, thank you so much, looks like I will be doing some research into variants.

Thanks again everyone!