Correct Macro so that if the person is not in the source document

[jeannie v]

Hi:

This is my macro that I need to fix....If the person does not have any data
on the "Daily Attendance" worksheet, I want it to skip that person's data on
their named worksheet....In other words, if Craig doesn't have anything on
Daily Attendance, I want it to go to the next worksheet and put the correct
data in for that person.....I hope this makes sense.......Any help would be
appreciated......

Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, "A").Value = "Lavendar, Craig" Then
v = Cells(i, "C").Value
Exit For
End If
Next
Sheets("Craig L").Activate
Range("N21").Value = v
Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, "A").Value = "Blackmon, Jack" Then
v = Cells(i, "C").Value
Exit For
End If
Next

 

[Socratis]

jeannie,

Here are two versions of the edited macro that do what you want.

1) Here you check against each name and if present in the Daily Attendance
sheet, then you update n21 on their named sheet. Note however, that there is
some redundancy in the code, not to mention that if you have a lot of names,
then you would have to type quit a few if statements.

Public Sub FillNames()
Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
With Cells(i, "A")
If .Value = "name1" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "name2" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "name3" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
End If
End With
Next
End Sub

thus, choice 2 which is shorter and does not require that you enter an if
for each name. In this version you must include all the names in the
attendance sheet, by leave the cell in column c empty.

Public Sub FillNames()
Dim lastRow As Integer

With Sheets("Daily Attendance")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With

For i = 1 To lastRow
With Sheets("Daily Attendance").Cells(i, "C")
If Not IsEmpty(.Value) Then
Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value
End If
End With
Next
End Sub

HTH.

Cheers,
Socratis

 

[jeannie v]

Good Morning, Socratis:

Thank you so much for your help......I will try both versions and see which
is the best for my use.....I will let you know if I have any problems since
I'm so new at macros.

I can't thank you enough for lending your talents and expertise.

 

[jeannie v]

Hi Socratis:

I have a problem....it won't pop the value in "C" on the Named Sheet.....I'm
using the first option you suggested since the 2nd option won't work....C
will always have a value and cannot be empty...the data is pulled at once
from another app and planted on the Daily Attendance sheet.

This is exactly what I did to modify the macro you recommended....could you
tell me what I'm dong wrong? I have checked and rechecked every naming
convention on the Daily Attendance sheet to be sure they are correct. When I
run the macro, I get an error message : Run Time Error 9 Subscript out of
range.....Remember that I am really new at macros and I know that you are
extremely sophisticated with them.......Thank you so much for all your help
and your patience....

Public Sub FillNames()
Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
With Cells(i, "A")
If .Value = "Perez, Arnaldis" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Johnson1, David" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Diaz 1, Henry" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Lafleur, Kimberlyn" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Wyatt1, Mariya" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Gonzalez, Merita" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Moyers1, Ricardo" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
ElseIf .Value = "Kelly, Robert" Then
Sheets(.Value).Range("n21").Value = Cells(i, "C").Value
End If
End With
Next
End Sub

 

[jeannie v]

One other thing: the Sheets Names or not the same naming convention as the
Daily Attendance sheet....i.e. Perez, Arnaldis on Daily Attendance is AJ P on
the Sheets Name.

Is this the problem?

 

[jeannie v]

Good Morning, Socratis:

I figured it out and your macro worked! Thank you so much for all your
help...I am thrilled that it is what I want.

Best Regards,

 

[jeannie v]

Hi Socratis:

I have another macro that is presenting a problem and I have tried several
people for help but they are making it really complicated and the macros
don't work....The problem is: I want the macros to look at Column A on
Worksheet 2 and select all incidence of a particular specialist's login info
in Columns A through L and then copy that data to named Worksheet 1 in Cells
A4 through L17.

The problem with the below macro is that the first instance pops in A4
through L4, but the rest of the incidents pop below in lines 40 something....

Can you help me?

Sub LoginLogoutMarquez()
'
' LoginLogoutMarquez Macro
' Macro recorded 6/23/2007 by Jeannie Vincovich
'
' Keyboard Shortcut: Ctrl+w
'

Dim n As Integer, i As Integer, j As Integer
Dim rng As Range

j = 0

Sheets("Agent Login-Logout").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Sheets("Agent Login-Logout").Activate
If Cells(i, "A").Value = "Diaz 1, Henry" Then
Range("A" & i & ":L" & i).Copy
Sheets("Henry D").Activate
If j = 0 Then
Set rng = Worksheets("Henry D").Range("A4")
rng.Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
j = j + 1
Else
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Set rng = Worksheets("Henry D").Range("A" & n)
rng.Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next i

End Sub