Thank-you for your help.
On Nov 3, 7:49 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> From your macro, I cannot for CERTAIN tell which sheet has this macro.
>
> From what I can see, you would have the "Input" sheet selected.
> There is a range named "CurrRec" in the sheet.
> You check to see if this range has been changed.
>
> To find the last row of the "PartsData" sheet using xlLastCell,
> it has to be SELECTED.
>
> So, you'll need to add a line:
>
> historyWks.Select
>
> before you use the ActiveCell.SpecialCells line.
>
> You must then remember to select the "Input" sheet.
>
> Now, to keep the user from seeing the screen from jumping back and forth,
> you can use:
>
> Application.ScreenUpdating = false (then set to true at the end)
>
> So you end up with:
>
> '=================================================================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim historyWks As Worksheet
> Dim inputWks As Worksheet
>
> Dim lRec As Long
> Dim lRecRow As Long
> Dim lLastRec As Long
> Dim lastRow As Long
>
> If Target.Address = Me.Range("CurrRec").Address Then
> Application.EnableEvents = False
> Application.ScreenUpdating = False
>
> Set inputWks = Worksheets("Input")
> Set historyWks = Worksheets("PartsData")
>
> historyWks.Select
> lastRow = ActiveCell.SpecialCells(xlLastCell).Row
> lLastRec = lastRow
>
> inputWks.Select
> With inputWks
> lRec = .Range("CurrRec").Value
> If lRec > 0 And lRec < lLastRec Then
> lRecRow = lRec + 1
> .Range("D5").Value = historyWks.Cells(lRecRow, 3)
> .Range("D7").Value = historyWks.Cells(lRecRow, 4)
> .Range("D9").Value = historyWks.Cells(lRecRow, 5)
> End If
> End With
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End If
> End Sub
>
> hope this helps,
>
> Paul
>
>
>
> ----- Original Message ----
> > From: JMac <jsvi...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Tue, November 2, 2010 3:17:06 PM
> > Subject: Re: $$Excel-Macros$$ Paste to Next empty Row
>
> > I used your suggestion, but still have the same issue. I've included
> > all the code - perhaps you some suggestions ?
>
> > Thanks
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
>
> > Dim historyWks As Worksheet
> > Dim inputWks As Worksheet
>
> > Dim lRec As Long
> > Dim lRecRow As Long
> > Dim lLastRec As Long
> > Dim lastRow As Long
>
> > If Target.Address = Me.Range("CurrRec").Address Then
> > Application.EnableEvents = False
>
> > Set inputWks = Worksheets("Input")
> > Set historyWks = Worksheets("PartsData")
>
> > With historyWks
> > lastRow = ActiveCell.SpecialCells(xlLastCell).Row
> > lLastRec = lastRow
> > End With
>
> > With inputWks
> > lRec = .Range("CurrRec").Value
> > If lRec > 0 And lRec < lLastRec Then
> > lRecRow = lRec + 1
> > .Range("D5").Value = historyWks.Cells(lRecRow, 3)
> > .Range("D7").Value = historyWks.Cells(lRecRow, 4)
> > .Range("D9").Value = historyWks.Cells(lRecRow, 5)
> > End If
> > End With
> > Application.EnableEvents = True
> > End If
>
> > End Sub
>
> > On Nov 2, 9:00 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> > > As I said, you can use the xlLastCell method.
> > > it considers cells that WERE used as "reserved".
> > > It's kind-f like if you were to hit "undo", the deleted rows would return,
> > > so it considers the deleted rows as "reserved" until "undo" is no longer
> > > possible, like when you exit and re-open the file.
>
> > > Another option would be to check multiple columns..
> > > but the same problem would exist. If the contents of THAT cell were
> > > removed....
>
> > > I suggest going with the:
> > > LastRow = ActiveCell.SpecialCells(xlLastCell).Row
>
> > > perhaps before saving you can sort the data so you can get rid of any
> > > empty rows that are created...
>
> > > Paul
>
> > > ----- Original Message ----
> > > > From: JMac <jsvi...@gmail.com>
> > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > Sent: Tue, November 2, 2010 7:53:25 AM
> > > > Subject: Re: $$Excel-Macros$$ Paste to Next empty Row
>
> > > > I beleive the problem is that if the data in column A gets deleted by
> > > > accident, it sees that row as empty and overwrites the whole row. Is
> > > > there a way to work around this ?
>
> > > > Thank-you
>
> > > > On Nov 1, 11:21 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> > > > > Jeff,
>
> > > > > There are lots of ways of finding the "next" empty row.
> > > > > In your case, is it possible that column "A" does not always have a
> >value?
> > > > > also... lLastRec = lastRow - 1 will give you the PREVIOUS row instead
> > > > > of
> >the
> > > > > NEXT row...
> > > > > Perhaps that's what is causing the problem.
> > > > > Wouldn't you use:
>
> > > > > lLastRec - lastRow + 1
>
> > > > > ??
>
> > > > > What I have often used is:
>
> > > > > LastRow = ActiveCell.SpecialCells(xlLastCell).Row
>
> > > > > Now, if during the execution of your macro, you're removing rows,
> > > > > then this method won't always work accurately.
> > > > > Let's say you have 10 rows of data.
> > > > > the above line will return 10.
> > > > > Now delete 5 rows.
> > > > > It will STILL Return 10.
> > > > > until you exit the file and re-open it.
> > > > > THEN it will return 5...
>
> > > > > Still...
>
> > > > > hope this helps,
>
> > > > > Paul
>
> > > > > ----- Original Message ----
> > > > > > From: JMac <jsvi...@gmail.com>
> > > > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > > > Sent: Mon, November 1, 2010 10:47:34 AM
> > > > > > Subject: $$Excel-Macros$$ Paste to Next empty Row
>
> > > > > > I've got a workbook in which a user inputs values into cells on
> > > > > > sheet
> > > > > > 1, and then the info is copied to a log sheet. I'm using the
> > > > > > following
> > > > > > code to find the next empty row in the log sheet to paste the info:
>
> > > > > > With historyWks
> > > > > > lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1,
> > > > > > 0).Row - 1
> > > > > > lLastRec = lastRow - 1
> > > > > > End With
>
> > > > > > From time to time, I'm told that an existing entry has been
> > > > > > overwritten with new data.
>
> > > > > > Should I be using something different to find the next empty row ?
>
> > > > > > Thanks
>
> > > > > > Jeff
>
> > > > > > --
>
> >>>----------------------------------------------------------------------------------
>
> > > > > >-
> > > > > > Some important links for excel users:
> > > > > > 1. Follow us on TWITTER for tips tricks and links :
> > > > > >http://twitter.com/exceldailytip
> > > > > > 2. Join our LinkedIN group
> > > > > > @http://www.linkedin.com/groups?gid=1871310
> > > > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > > > Like our page on facebook , Just follow below link
>
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
> > > >Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > --
>
> >>----------------------------------------------------------------------------------
>
> > > >-
> > > > Some important links for excel users:
> > > > 1. Follow us on TWITTER for tips tricks and links :
> > > >http://twitter.com/exceldailytip
> > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > Like our page on facebook , Just follow below link
> > > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
> >Hide quoted text -
>
> > > - Show quoted text -
>
> > --
> >----------------------------------------------------------------------------------
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&...-
> >Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
