|
Need to fix macro to list data underneath each other
Hi :
I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Jeannie
A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Try this
Option Explicit Sub testjanedoe() Const nameTestCol = "A" Const destCol = "Q" Const transSheetName = "Transactions" Const janeDSheetName = "Jane D" Dim transWS As Worksheet Dim janeDWS As Worksheet Dim i As Long Dim lastRow As Long Dim janeLastRow As Long Dim getName As String getName = InputBox("Enter Name", "Get Name", "<Enter Name") Set transWS = Worksheets(transSheetName) Set janeDWS = Worksheets(janeDSheetName) lastRow = transWS.Cells(Rows.Count, nameTestCol).End(xlUp).Row janeLastRow = janeDWS.Cells(Rows.Count, destCol).End(xlUp).Row + 1 For i = 1 To lastRow If transWS.Range(nameTestCol & i).Value = getName Then janeDWS.Range(destCol & i).Offset(4, 0).Value = _ transWS.Range(nameTestCol & i).Offset(0, 1).Value janeDWS.Range(destCol & i).Offset(4, 1).Value = _ transWS.Range(nameTestCol & i).Offset(0, 2).Value End If Next i End Sub "jeannie v" wrote: Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
I see Roger beat me to it. An alternative to Roger's follows. Assumed is that
you don't actually want to hardcode the person's name ("Jane Doe") nor the target worksheet name ("Jane D"). This lets you specify the name and confirm the target worksheet name before making the transfer. Sub Transactions2() Dim ws1 As Worksheet, ws2 As Worksheet Dim c As Range Dim i As Long, rw As Long Dim nm As String, wsnm As String, addr As String Dim msg As String, ttl As String msg = "Step 1:" & vbCr & vbCr & "Enter name to search..." ttl = "Transactions search" nm = InputBox(msg, ttl) If Len(nm) = 0 Then Exit Sub i = InStr(nm, " ") msg = "Step 2:" & vbCr & vbCr & "Confirm summary page name..." wsnm = InputBox(msg, ttl, Left(nm, i + 1)) If Len(wsnm) = 0 Then Exit Sub DoEvents Set ws1 = Sheets("Transactions") On Error Resume Next Set ws2 = Sheets(wsnm) If Err.Number < 0 Then msg = "Error: Summary page for " & wsnm & " does not exist " MsgBox msg, vbCritical, ttl Exit Sub End If On Error GoTo 0 rw = ws2.Cells(Rows.Count, "Q").End(xlUp).Row + 1 rw = IIf(rw < 4, 4, rw) Set c = ws1.Columns(1).Find(nm, LookAt:=xlWhole) If Not c Is Nothing Then addr = c.Address ws2.Cells(rw, "Q").Resize(1, 2).Value = c(1, 17).Resize(1, 2).Value Else msg = "Name not found in transactions list " MsgBox msg, vbExclamation, ttl Exit Sub End If i = 1 Do Set c = ws1.Columns(1).FindNext(c) If c.Address < addr Then _ ws2.Cells(rw + i, "Q").Resize(1, 2).Value = c(1, 17).Resize(1, 2).Value i = i + 1 Loop While c.Address < addr Set ws1 = Nothing: Set ws2 = Nothing: Set c = Nothing End Sub |
Need to fix macro to list data underneath each other
Sorry. I misread the post. Should be:
Sub Transactions2() Dim ws1 As Worksheet, ws2 As Worksheet Dim c As Range Dim i As Long, rw As Long Dim nm As String, wsnm As String, addr As String Dim msg As String, ttl As String msg = "Step 1:" & vbCr & vbCr & "Enter name to search..." ttl = "Transactions search" nm = InputBox(msg, ttl) If Len(nm) = 0 Then Exit Sub i = InStr(nm, " ") msg = "Step 2:" & vbCr & vbCr & "Confirm summary page name..." wsnm = InputBox(msg, ttl, Left(nm, i + 1)) If Len(wsnm) = 0 Then Exit Sub DoEvents Set ws1 = Sheets("Transactions") On Error Resume Next Set ws2 = Sheets(wsnm) If Err.Number < 0 Then msg = "Error: Summary page for " & wsnm & " does not exist " MsgBox msg, vbCritical, ttl Exit Sub End If On Error GoTo 0 rw = ws2.Cells(Rows.Count, "Q").End(xlUp).Row + 1 rw = IIf(rw < 4, 4, rw) Set c = ws1.Columns(1).Find(nm, LookAt:=xlWhole) If Not c Is Nothing Then addr = c.Address ws2.Cells(rw, "Q").Resize(1, 2).Value = c(1, 2).Resize(1, 2).Value Else msg = "Name not found in transactions list " MsgBox msg, vbExclamation, ttl Exit Sub End If i = 1 Do Set c = ws1.Columns(1).FindNext(c) If c.Address < addr Then _ ws2.Cells(rw + i, "Q").Resize(1, 2).Value = c(1, 2).Resize(1, 2).Value i = i + 1 Loop While c.Address < addr Set ws1 = Nothing: Set ws2 = Nothing: Set c = Nothing End Sub |
Need to fix macro to list data underneath each other
Hi Roger:
Thank you so much! It works beautifully EXCEPT it addes the first record back to to the end of the list of transactions in Q & R-- .......other than that this is excatly what I was looking for. Could you help me get rid of that last entry which duplicates the first entry? jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It
works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Jeannie
You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Option Explicit
Sub testjanedoe() Const nameTestCol = "A" Const destCol = "Q" Const transSheetName = "Transactions" Const janeDSheetName = "Jane D" Dim transWS As Worksheet Dim janeDWS As Worksheet Dim i As Long Dim lastRow As Long Dim janeLastRow Dim getName As String getName = UCase(InputBox("Enter Name", "Get Name", "<Enter Name")) Set transWS = Worksheets(transSheetName) Set janeDWS = Worksheets(janeDSheetName) lastRow = transWS.Cells(Rows.Count, nameTestCol).End(xlUp).Row For i = 1 To lastRow If UCase(transWS.Range(nameTestCol & i).Value) = getName Then janeLastRow = FindLastRow(janeDWS, destCol) + 1 janeDWS.Range(destCol & janeLastRow).Value = _ transWS.Range(nameTestCol & i).Offset(0, 1).Value janeDWS.Range(destCol & janeLastRow).Offset(0, -1).Value = _ transWS.Range(nameTestCol & i).Value janeDWS.Range(destCol & janeLastRow).Offset(0, 1).Value = _ transWS.Range(nameTestCol & i).Offset(0, 2).Value End If Next i End Sub Private Function FindLastRow(whatSheet As Worksheet, whichCol As String) As Long If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel FindLastRow = whatSheet.Range(whichCol & Rows.Count).End(xlUp).Row Else 'in Excel 2007 or later FindLastRow = whatSheet.Range(whichCol & Rows.CountLarge).End(xlUp).Row End If If FindLastRow = 1 And IsEmpty(whatSheet.Range(whichCol & "1")) Then FindLastRow = 3 ' no entries at all in the column on the sheet End If End Function "jeannie v" wrote: Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Roger: I need further help on the first macro.....it works fine for 1
person, but when I copy it and add the macro for another individual, it doesn't work.....I'm really new at this macro stuff, so be gentle...... -- jeannie v "Roger Govier" wrote: Hi Jeannie You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Roger: On the second macro shown below.....the last line pops in A4:L4,
but then all of the other lines of data pop in lines 43 through 46...Can you help with this? If you want me to send the workbook, I will. -- jeannie v "Roger Govier" wrote: Hi Jeannie You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Roger: Well.....I was able to fix the Transactions Macro and I can't
thank you enough for your help.....The last piece is the LoginLogout.....I am so close with the macro you provided, I just need it to pop up in A4 to L17 rather than down in lines 43 down....... I hope and pray you are able to help me with this.....it can't be that hard, right? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Jeannie
Send me the file . To send direct, remove NOSPAM from my email address -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: On the second macro shown below.....the last line pops in A4:L4, but then all of the other lines of data pop in lines 43 through 46...Can you help with this? If you want me to send the workbook, I will. -- jeannie v "Roger Govier" wrote: Hi Jeannie You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Hi Roger:
Don't laugh.......how do I remove NO SPAM from your email address and to what address do I send it? I have previously just sent to email addresses of the Group Experts from Outlook......I'm really new at this Group Community stuff, so bear with me. Thank you, -- jeannie v "Roger Govier" wrote: Hi Jeannie Send me the file . To send direct, remove NOSPAM from my email address -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: On the second macro shown below.....the last line pops in A4:L4, but then all of the other lines of data pop in lines 43 through 46...Can you help with this? If you want me to send the workbook, I will. -- jeannie v "Roger Govier" wrote: Hi Jeannie You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
|
Need to fix macro to list data underneath each other
Hi Jeannie
File received, fixed and returned. The macro was selecting row 43, because you had entries below you range of A4:L17, and starting from the end of sheet and working up, it found entries in A42, hence made A43 the first row. Calculating "j" for the row number to start, has been changed to j = WorksheetFunction.CountA(Range("A3:A17")) + 4 Rather than repeating the code with different names and different worksheets, I added a new sheet called List, which holds in columns A and B the names and Sheetnames for the people whose data you want to collect. There is an outer loop, which works through each name and sheet in turn. Hopefully this resolves all of your outstanding issues. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Jeannie In order to avoids the spammers, most of us disguise our email address. If you remove NOSPAM from the above, it will work -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: Don't laugh.......how do I remove NO SPAM from your email address and to what address do I send it? I have previously just sent to email addresses of the Group Experts from Outlook......I'm really new at this Group Community stuff, so bear with me. Thank you, -- jeannie v "Roger Govier" wrote: Hi Jeannie Send me the file . To send direct, remove NOSPAM from my email address -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: On the second macro shown below.....the last line pops in A4:L4, but then all of the other lines of data pop in lines 43 through 46...Can you help with this? If you want me to send the workbook, I will. -- jeannie v "Roger Govier" wrote: Hi Jeannie You're very welcome. For your subsequent request, try the following. I'm not sure whether you are saying the same sheet names or not. Anyway, wss is the Source sheet and wsd is the Destination sheet, so just change the sheet names in the Set statements as appropriate. Sub Transactions2() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("A" & i & ":L" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi Roger: I'm sorry.....my bad.......Ihad the transaction in twice....It works beautifully........I can't thank you enough for lending your talents and expertise. I have another project that it basically the same thing....except instead of the data in B&C on Transactions, it is A through L AND on the person's sheet I want that data to appear in A4 through L17...Is there a way to modify this macro to do that for my LoginLogout Sheet? -- jeannie v "Roger Govier" wrote: Hi Jeannie A couple of problems. You are using the same variable twice ("n") to count the row numbers. Also, when counting for Sheet Jane D, you are counting in column A, not column Q. Here is your code amended, so that it works. I have also shown a shortened version below, which avoids all of the selecting, and runs much quicker. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate j = Cells(Rows.Count, "Q").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & j) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub And here is the slightly more efficient code to do the same task. Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet Set wss = Sheets("Transactions") Set wsd = Sheets("Jane D") n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = wsd.Cells(Rows.Count, "Q").End(xlUp).Row + 1 j = WorksheetFunction.Max(4, j) Set dest = wsd.Range("Q" & j & ":R" & j) If wss.Cells(i, "A").Value = "Jane Doe" Then wss.Range("B" & i & ":C" & i).Copy dest dest = dest.Value End If Next i End Sub -- Regards Roger Govier "jeannie v" wrote in message ... Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
Need to fix macro to list data underneath each other
Thank you, Roger for assisting me with this one....Everything works great!
-- jeannie v "jeannie v" wrote: Hi : I want this macro to give me the data in Columns B & C of a particular person in Column A on "Transactions" Worksheet and enter in it Q4 and R4 on the Named Worksheet. What happens is that it doesn't list all of the transactions for that person one underneath the other in Q4 and R4....it enters the first transaction in Q4 & R4 but the just enters the last transaction in Q44 & R44...with no transactions in between or underneath each other. Can someone help with this problem: Sub Transactions() ' ' Transactions Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+n ' Dim n As Integer, i As Integer Dim rng As Range Sheets("Transactions").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Transactions").Activate If Cells(i, "A").Value = "Jane Doe" Then Range("B" & i & ":C" & i).Copy Sheets("Jane D").Activate If Range("Q4") = "" Then Set rng = Worksheets("Jane D").Range("Q4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else Sheets("Jane D").Activate n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Jane D").Range("Q" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v |
| All times are GMT +1. The time now is 07:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com