Macro with Variable Data

[jeannie v]

Hi: I apologize that I am a real novice in Macros.

This is the macro that works great, but I want to tweak it just a little.

Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, "A").Value = "Glover, Ebony" Then
v = Cells(i, "C").Value
Exit For
End If
Next
Sheets("Ebony G").Activate
Range("N21").Value = v


I want to create a macro to do almost the same thing, except I want it to
retrieve more than 1 field of data for the person...Can I tweak this one to
do what I want?

I want it to say say: If "Glover, Ebony" is found by auto-filter in Column A
from Cell A2:A160 in Agent Login-Logout, Take the data in Column A through
Column L and copy to Worksheet 1 "Ebony G" in Cells A4 to L14.

Any help would be appreciated.

 

[Ariel Dugan]

Hi Jeannie,

Hope this helps...

Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, "A").Value = "Glover, Ebony" Then

Range("A" & i & ":L" & i).copy

Exit For
End If
Next
Sheets("Ebony G").Activate

ActiveSheet.Paste Destination:=Worksheets("Ebony G").Range("A4")

End Sub

 

[jeannie v]

Hi Ariel:

Thank you for your help.....and we're almost there....your recommendation
only popped 1 line of data for Ebony when there should have been 5 lines that
copied over to Worksheet 1.....can you help further?

 

[Ariel Dugan]

Did you want the next four lines down?

If so, then replace this line

Range("A" & i & ":L" & i).copy

with this line

Range("A" & i & ":L" & i +4).copy

and that should work.

HTH
Ariel

 

[jeannie v]

Hi Ariel:

You've been great and very patient with me....

The problem is that the number of lines per specialist will vary....is there
a way to say: select all "Ebony Glover" from A to L and copy to the range on
Worksheet 1 from A4 to L13....it may not fill the entire range, but will log
all of Ebony's loggings for that day.

Does this make sense?

 

[jeannie v]

Hi Ariel:

I figured it out....OMG it was so easy...i feel foolish....Thank you for all
your help....You are a wonder.

 

[jeannie v]

Hi Ariel: Me again...What can I add to the macro to Paste Special Values
when it pops in worksheet 1?

 

[Ariel Dugan]

Hi Jeannie,

I hope I understood this time. This checks each cell in Row A:A on the
"Attendance Sheet", and if the value is "Glover, Ebony", then it copies the
range and pastes in Row 4:4 or below in the other sheet.

Sub GetData()
Dim n As Integer, i As Integer, j As Integer

j = 0

Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Sheets("Daily Attendance").Activate
If Cells(i, "A").Value = "Glover, Ebony" Then
Range("A" & i & ":L" & i).Copy
Sheets("Ebony G").Activate
If j = 0 Then
ActiveSheet.Paste Destination:=Worksheets("Ebony
G").Range("A4")
j = j + 1
Else
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveSheet.Paste Destination:=Worksheets("Ebony
G").Range("A" & n)
End If
End If
Next i

 

[Ariel Dugan]

Hi Jeannie,

This worked for me.

Sub GetData()
Dim n As Integer, i As Integer, j As Integer
Dim rng As Range

j = 0

Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Sheets("Daily Attendance").Activate
If Cells(i, "A").Value = "Glover, Ebony" Then
Range("A" & i & ":L" & i).Copy
Sheets("Ebony G").Activate
If j = 0 Then
Set rng = Worksheets("Ebony G").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("Ebony G").Range("A" & n)
rng.Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next i



End Sub

 

[jeannie v]

Hi Ariel:

Well...this didn't work at all.....the previous recommendation did work, but
only for Ebony. When I created the macro for the other specialists, it added
Ebony's data rather than theirs--

Let me see if I can explain what I'm looking for it to do:

I have several specialists in Worksheet 2....I have it set to
auto-filter....I want to pull each specialist's login/logout times for the
day from Worksheet 2 and send to Worksheet 1 in A4 through L13....Worksheet 2
has the data in Columns A through L...I need to pop the specialist's name
from Worksheet 2 Column A and all of their data through Column L....there is
more than 1 line of data on Worksheet 2 for each specialist....This is what I
need it to look like for each specialist who have their own worksheets in the
workbook...i.e. 7 Worksheets....

Worksheet 1(Ebony G)
Glover, Ebony 7616 1181892640 7:30AM 1181898005 9:00AM 6/14/2007 6/15/2007 27
Glover, Ebony 7616 1181898907 9:15AM 1181901580
9:59AM 6/14/2007 6/15/2007 27
Glover, Ebony 7616 1181907231 11:33AM 1181912507
1:01PM 6/14/2007 6/15/2007 27
Glover, Ebony 7616 1181914253 1:30PM 1181915257 1:47PM 6/14/2007 6/15/2007 27


Worksheet 2 (Source Date) Ebony and....sample of the below additional
specialists..ther are 40 of them....
Gonzalez, Iro 7569 1181892612 7:30AM 1181893051 7:37AM 39247 6/15/2007 27
Agosto, Helen 7560 1181892684 7:31AM 1181898055 9:00AM 392476/15/2007 444 27

etc.

Do you think you cam help me?
jeannie v

 

[jeannie v]

Good Morning, Ariel:

We are getting so close I can taste it.....It works so far for Ebony except
that the first line of data extracted is pasted where it's supposed to be,
but the other lines paste at the bottom of my spreadsheet with blank lines in
between....I want the data lines that are extracted to appear on "Ebony G"
worksheet right underneath each other with no blank lines. How can we do
that?

 

[Ariel Dugan]

Sorry,

I am a novice myself. Here is code that works properly, I hope. Notice I
got rid of that J variable, and instead am just checking in "A4" is blank to
decide where to start the pasting, and had to activate the "Ebony G" sheet in
the "Else" section of the "If" structure.

Seems to work now. Let me know.

Sub GetData()
Dim n As Integer, i As Integer
Dim rng As Range



Sheets("Daily Attendance").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Sheets("Daily Attendance").Activate
If Cells(i, "A").Value = "Glover, Ebony" Then
Range("A" & i & ":L" & i).Copy
Sheets("Ebony G").Activate
If Range("A4") = "" Then
Set rng = Worksheets("Ebony G").Range("A4")
rng.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Else
Sheets("Ebony G").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Set rng = Worksheets("Ebony G").Range("A" & n)
rng.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
End If
Next i

End Sub

 

[jeannie v]

Hi Ariel:

Nope it still does the same thing....It will paste the first incident for
Ebony as it should, then pastes the 4 remaining lines down on 44 thru
47.....I want it to paste from A4 to L4, A5 to L5, A6 to L6, etc--

We are so close.....

jeannie v

 

[jeannie v]

Hi Ariel:

I apologize...I was doing something wrong.....I found my error and you last
response worked beautifully, I think...I haven't copied it for all the
specialists yet...But I see that Ebony is working just as it's supposed to.

I can't tell you how much I appreciate your talents and your help.

Thank you,

 

[AD108]

Glad I could help,

Thanks

Ariel

Hi Ariel:

I apologize...I was doing something wrong.....I found my error and you
last
response worked beautifully, I think...I haven't copied it for all the
specialists yet...But I see that Ebony is working just as it's supposed
to.

I can't tell you how much I appreciate your talents and your help.

Thank you,

 

[jeannie v]

Hi Ariel:

I'm sorry....I don't know what I'm doing wrong, but the macro doesn't
work.....could I send the workbook to you so you can see what happens when I
run the macro? It will pop the first incident of the specialist on the
Worksheet, but the rest of the incidents for that specialist pop further down
on the page.

Any help you can provide would be appreciated.