How to create an Update Macro

[bigsistar12]

I have two worksheets, one is a fillable form (calculator) and the other one works as a database.

First I fill out the calculator and save the current new record to my database.
The database will constantly have new records but will be used to update old records too.

My calculator has a save as button that saves new records. My database has a open button that opens old records for updating or editing. I need a macro that allows me (after opening an old record) to save the current records updates or changse to the original record and not create a new one. Same concept as Save as vs Save.

Im not really sure where even to begin. The reason is because each record may have same name different values ex. Dave smith (dates of service) 10/21/10 $50, Dave smith (dates of service) 11/03/10 $2. I think the best way to reference the originting cell is to reference either the active cell in my database from which the original data was pulled from and then in the macro just update the dates or amounts. Or to update a record by two variables such as name and date.

Anyway to do this?

 

[3link]

How are the records stored on the 'database' sheet? Is it one row per record? So it'd be a row like:

Data189781 Column 1 Column 2 Column3

I don't know about anyone else, but I need more info before I can help. I need to know how the records are stored so I can figure out how to "save" instead of "save as".

 

[bigsistar12]

Ok my records are stored as follows
A B C D
Name DOS AMT Product
Davd 5/10/10 $50 Toilet Paper
Davd 5/10/10 $100 Shoes

So If I need to update only the shoes record but david smith is in my databse twice then I need the macro to remember where i originally got my information from and compare and change only the information that is new.

 

[Marcelo Branco]

Hi,

Maybe the built-in DataForm can help. Take a look at

http://office.microsoft.com/en-us/e...te-rows-by-using-a-data-form-HP010236698.aspx

HTH

M.

 

[3link]

Ok my records are stored as follows
A B C D
Name DOS AMT Product
Davd 5/10/10 $50 Toilet Paper
Davd 5/10/10 $100 Shoes

So If I need to update only the shoes record but david smith is in my databse twice then I need the macro to remember where i originally got my information from and compare and change only the information that is new.

Does each row have at least one unique value (order / entry / batch #)?

 

[bigsistar12]

Does each row have at least one unique value (order / entry / batch #)?

Such as Shoes will always be shoes but the dates of service and amounts are the only things that change? Yes there will be two unique values, the name and the product.

Im guessing it would be something like this

xlCellTypeLastCell. The last cell in the used range

 

[bigsistar12]

Hi,

Maybe the built-in DataForm can help. Take a look at

http://office.microsoft.com/en-us/e...te-rows-by-using-a-data-form-HP010236698.aspx

HTH

M.

thanks Marcelo, but the information is so specific that I need to create my own form and database.

 

[3link]

Such as Shoes will always be shoes but the dates of service and amounts are the only things that change? Yes there will be two unique values, the name and the product.

Im guessing it would be something like this

xlCellTypeLastCell. The last cell in the used range

This is still hard to follow. If every order is stored in a row, and every order/row has a unique value, why not just find that row and make the changes manually? How does the database retrieve old orders?

I guess this is over my head.

 

[bigsistar12]

Example If I Create a new document I would save it as Save as new document.

but if I open a document from My documents, change data within the document all I would do is just hit save and it would save the new info on the old document only updating the information that changed. Not creating a new document.

I guess Im not sure how to write the macro or update the macro that I have to say "open the record containg all information from A1-AE1; all information that changes in second spreadsheet will autmatically be updated on original sheet in the record that it was originally found in."


This is the macro I use to open the information from the original document to my calculator. All I want to do now is after I open the info; is for Excel to automatically update and change any info that I change while that record is open in my second spreadsheet.

Sub NewPatient()

Sheets("Insurance1").Rows("3:3").Insert Shift:=xlDown
i = 0
Set myAnchor = Worksheets("insurance1").Range("a3")
Application.ScreenUpdating = False
For Each cll In Worksheets("pricingcalculator").Range("B1,G1,B2,G2,B3,G3,B4,F4,H4,B5,F5,H5,B6,F6,H6,B8,F8,H8,B9,F9,H9,B10,F10,H10,B11,F11,H11,B12,F12,H12,B13,B14").Cells
myAnchor.Offset(, i).Value = cll.Value
i = i + 1
Next cll
myAnchor.Parent.Hyperlinks.Add Anchor:=myAnchor, Address:="", SubAddress:=myAnchor.Resize(, 32).Address(False, False, ReferenceStyle:=xlR1C1, relativeto:=myAnchor), TextToDisplay:=CStr(myAnchor.Value)
Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

 

[Marcelo Branco]

thanks Marcelo, but the information is so specific that I need to create my own form and database.

A pity, because the built-in DataForm is a good and pratical tool to insert/delete or update records.

Let me ask a question: what is so specific that the DataForm cant handle?

M.