Sleeper: Updating and copying?

[erin64]

Is it possible (of course it is..., but) to make a simple macro (or some other way) to copy all the changed records to sheet2. I mean the whole row....
And how does this clever Xl-alien in my computer now that at first time one row is copied to row1 but next time it is row2 and so on...

And 1 more thing... I have a function in one cell that shoud be the date the record was created, but it keeps changing...

sorry my english and confused explanations

-erin-

[Zack Barresse]

Hello Erin, welcome to VBAX!

Do you think you can give an example of what you mean? I think I understand, but it is still a little unclear at the moment. It sounds like you need a worksheet_change event, but I'm not sure.

If you need to, you can post an example spreadsheet (zipped) to the board.

[erin64]

Morning Firefytr and thanks of wellcoming me here... You'll be regretting it

I'm trying to make our customer records easier to handle...

This far I've made a little progress. I manage to find sand save some of shanged data into a new sheet, and save it as a new workbook...

But now still two problems occur:

1. It searches the sheet where it'scathering the found info, so there are dublicates.
2.
And it still open a new book automatically, what I don't want, because I made a button that saves only when needed...
3. It doesnt search all cels in workbook, and I m unseure which numbers to change...

Sub SearchandCopy()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
Dim rCopyCells As Range
On Error GoTo Err
Application.ScreenUpdating = False
    Sheets("Temp").Select
    Range("A2:D1000").Select
    Selection.ClearContents
    Sheets("Vero").Select
Application.ScreenUpdating = True
WhatToFind = Application.InputBox("Kirjoita nimi?", "Etsi", , 100, 100, , , 2)
If WhatToFind = False Then
Sheets("Vero").Select
End
End If
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
If MsgBox("Add Record", vbInformation + vbYesNo) = vbYes Then
ActiveCell.Select
Selection.EntireRow.Copy Destination:= _
Sheets("Temp").Range("A65536").End(xlUp).Offset(1, 0)
Set rCopyCells = Nothing
End If
On Error Resume Next
While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
If MsgBox("Add Record", vbInformation + vbYesNo) = vbYes Then
ActiveCell.Select
Selection.EntireRow.Copy Destination:= _
Sheets("Temp").Range("A65536").End(xlUp).Offset(1, 0)
End If
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
Application.ScreenUpdating = False
 Sheets("Temp").Select
 Range("A2,AA1300").Select
 Application.ScreenUpdating = True
 Sheets("Vero").Select
 Sheets("Temp").Copy
 End
Err:
MsgBox "Sorry, virhe. Yrit? uudelleen!"
End
End Sub

So as you see, a true beginner here hoping for help....

[erin64]

Some progress made, but new problems occured...