PDA

View Full Version : Sleeper: Updating and copying?



erin64
02-01-2005, 02:21 PM
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 :rolleyes:

-erin-

Zack Barresse
02-01-2005, 09:59 PM
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
02-01-2005, 10:55 PM
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...:think:


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
02-02-2005, 09:18 AM
Some progress made, but new problems occured...:(