>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 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
>
--
----------------------------------------------------------------------------------
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
