Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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. -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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" wrote: 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. -- jeannie v |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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" wrote: 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. -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
Hi Ariel: Me again...What can I add to the macro to Paste Special Values
when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "jeannie v" wrote: 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 "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "Ariel Dugan" wrote: 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 "jeannie v" wrote: 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 "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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" wrote: 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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "Ariel Dugan" wrote: 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" wrote: 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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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 "Ariel Dugan" wrote: 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" wrote: 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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
Glad I could help,
Thanks Ariel "jeannie v" wrote in message ... 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 "Ariel Dugan" wrote: 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" wrote: 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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro with Variable Data
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. -- jeannie v "AD108" wrote: Glad I could help, Thanks Ariel "jeannie v" wrote in message ... 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 "Ariel Dugan" wrote: 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" wrote: 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? -- jeannie v "Ariel Dugan" wrote: 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" wrote: Hi Ariel: Me again...What can I add to the macro to Paste Special Values when it pops in worksheet 1? -- jeannie v "Ariel Dugan" wrote: 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" wrote: 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. -- jeannie v |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Data within a Macro | Excel Worksheet Functions | |||
Subtotaling variable data in a macro | New Users to Excel | |||
PAUSE EXCEL MACRO FOR INPUT OF DATA VARIABLE | Excel Discussion (Misc queries) | |||
Variable Macro | Excel Discussion (Misc queries) | |||
Macro - Data Sort -Variable Selection | Excel Discussion (Misc queries) |